Postgres - query JSON column value of nested object

Question

I'm using following schema for the JSONB column of my table (named fields). There are several of these field entries.

{
   "FIELD_NAME": {
      "value" : "FIELD_VALUE",
      "meta": {
         "indexable": true
      }
   }
}

I need to find all the fields that contain this object

 "meta": {
    "indexable": true
 }

Here is a naive attempt at having json_object_keys in where clause, which doesn't work, but illustrates what I'm trying to do.

with entry(fields) as (values('{
  "login": {
    "value": "fred",
    "meta": {
      "indexable": true
    }
  },
  "password_hash": {
    "value": "88a3d1c7463d428f0c44fb22e2d9dc06732d1a4517abb57e2b8f734ce4ef2010",
    "meta": {
      "indexable": false
    }
  }
}'::jsonb))
select * from entry where fields->jsonb_object_keys(fields) @> '{"meta": {"indexable": "true"}}'::jsonb;

How can I query on the value of nested object? Can I somehow join the result of json_object_keys with the table iself?

Answer 1

demo:db<>fiddle

First way: using jsonb_each()

SELECT
    jsonb_build_object(elem.key, elem.value)        -- 3
FROM
    entry,
    jsonb_each(fields) as elem                      -- 1
WHERE
    elem.value @> '{"meta": {"indexable": true}}'   -- 2

1. Expand all subobjects into one row per "field". This creates 2 columns: the key and the value (in your case login and {"meta": {"indexable": true}, "value": "fred"})
2. Filter the records by checking the value column for containing the meta object using the @> as you already mentioned
3. Recreate the JSON object (combining the key/value columns)

---

Second way: Using jsonb_object_keys()

SELECT
    jsonb_build_object(keys, fields -> keys)            -- 3
FROM
    entry,
    jsonb_object_keys(fields) as keys                   -- 1
WHERE 
    fields -> keys @> '{"meta": {"indexable": true}}'   -- 2

1. Finding all keys as you did
2. and 3. are very similar to the first way