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?

@S-Man nope, that was an error. Thanks for pointing it out. Fixed. — brabec
– brabec, Commented Sep 13, 2019 at 14:03
Do you want the whole object "{login: {...}}" or only the key "login" — S-Man
– S-Man, Commented Sep 13, 2019 at 14:04

S-Man · Accepted Answer · 2019-09-13 14:16:21Z

2

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

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"})
Filter the records by checking the value column for containing the meta object using the @> as you already mentioned
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

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

edited Sep 13, 2019 at 14:16

answered Sep 13, 2019 at 14:10

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - query JSON column value of nested object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related