Postgres JSONB query about nested/recursive elements

Question

I have a nested and hierarchical structure expressed in JSON e.g.:

{
   "id":1,
   "children": [
      { "id":2 },
      { "id": 3, "children": [
         { "id": 4 }
         ]
      }
   ]
}

Can postgres answer a query whether the record contains "id": 4 in any part of the document?

If yes, are such queries backed by JSONB indexing added in version 9.4?

Sadly, I tried tackling this problem with 9.3, however I ran into issues with getting the same code to run on objects and arrays. I actually think this might be a lot easier in 9.4. I started writing a recursive query to get it done, but alas, I've failed-- for today anyway. — Joe Love
– Joe Love, Commented Jan 2, 2015 at 11:56
Well nevermind, I figured out a hack to detect whether a json field was an array or object based on the first character of its textual representation. — Joe Love
– Joe Love, Commented Jan 2, 2015 at 12:35
May not directly answer your question, but I use JSONSelect and PLV8 to handle my more complex JSON traversal and querying tasks. Took some time to get set up, but it's working well for us. — trvrm
– trvrm, Commented Jan 3, 2015 at 20:28

Joe Love · Accepted Answer · 2015-01-03 22:08:06Z

2

UPDATE: Thanks to therealgaxbo on reddit, we started with my original code and developed something more concise:

with recursive deconstruct (jsonlevel) as(
    values ('{"id":1,"children":[{"id":2},{"id":3,"children":[{"id":4}]}]}'::json)

    union all

    select 
        case left(jsonlevel::text, 1)
            when '{' then (json_each(jsonlevel)).value
            when '[' then json_array_elements(jsonlevel)
        end as jsonlevel
    from
        deconstruct
    where
        left(jsonlevel::text, 1) in ('{', '[')
)
select * from deconstruct where case when left(jsonlevel::text, 1) = '{' then jsonlevel->>'id' = '4' else false end;

My original response below:

I experimented like crazy and finally came up with something like this:

with recursive ret(jsondata) as
(select row_to_json(col)::text jsondata from 
json_each('{
   "id":1,
   "children": [
      { "id":2 },
      { "id": 3, "children": [
         { "id": 4 }
         ]
      }
   ]
}'::json) col
union 
select case when left(jsondata::text,1)='[' then row_to_json(json_each(json_array_elements(jsondata)))::text
when left((jsondata->>'value'),2)='{}' then null::text
when left((jsondata->>'value')::text,1)='[' then row_to_json(json_each(json_array_elements(jsondata->'value')))::text
else  ('{"key":'||(jsondata->'key')||', "value":'||(jsondata->'value')||'}')::json::text end jsondata 
from (
select row_to_json(json_each(ret.jsondata::json)) jsondata
from ret) xyz

)
select max(1) from ret
where jsondata::json->>'key'='id' 
and jsondata::json->>'value'='1'

edited Jan 3, 2015 at 22:08

answered Jan 2, 2015 at 12:31

Joe Love

6,1262 gold badges24 silver badges33 bronze badges

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

4 Comments

Joe Love Over a year ago

please feel free to edit this, I'd actually like to see improvements done, or for you to at least point out any errors when you throw a ton of varying data at it.

Karol Kolenda Over a year ago

Thank you Joe for your code. To be honest your SQL just confirmed my concerns - it is much too complex for me to justify it - I don't want everyone in the team to become Postgres guru in order to understand what's going on. I guess I have two options: flatten my JSON hierarchy to a simple list with ParentID or just forget about doing this in Postgres and query things on application level.

Joe Love Over a year ago

With 9.4 and jsonb, it'll be a lot easier.. now, you could easily write a function to hide some of the complexity if needed.

Joe Love Over a year ago

Also, if you're willing to install the V8 language, it has the power of javascript, which is even more JSON capable.

Collectives™ on Stack Overflow

Postgres JSONB query about nested/recursive elements

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related