query for ids inside JSONB using jsonb_each and LATERAL in postgres

Question

Imagine a super simple table such as:

create table object (
    id INT,
    data jsonb
);

With some data:

INSERT INTO object (id, data) VALUES
  (4, '{"nodes":{"f":{"id":1}}}'), -- C
  (5, '{"nodes":{"d":{"id":2}, "e":{"id":3}}}'), -- B
  (6, '{"nodes":{"b":{"id":4}, "c":{"id":5}}}') -- A
;

I'd like to destructure the JSON and also query for children.

For example, if I do

SELECT * FROM jsonb_each('{"a":{"id":1},"b":{"id":2}}'::JSONB) as obj

I will get back:

a   {"id":1}
b   {"id":2}

I'm trying to combine this to get id properties out of the nested objects and query for children (with no luck):

SELECT
      jsonb_each(data->'nodes')
      FROM objects as objs
      WHERE id=6
      LATERAL (SELECT * FROM objects as ref WHERE ref.id = objs->'id');

I've provided an SQL fiddle if it helps: http://sqlfiddle.com/#!17/50fb2/9

EDIT:

here's an example output:

id  data
4   '{"nodes":{"f":{"id":1}}}'
5   '{"nodes":{"d":{"id":2}, "e":{"id":3}}}'

Thanks again for any insight into this!

Please, edit the question and add an example output.

klin
– klin

2017-06-18 17:34:21 +00:00
Commented Jun 18, 2017 at 17:34 — klin
– klin, Commented Jun 18, 2017 at 17:34
@klin I just added example output above, thanks!

pyramation
– pyramation

2017-06-18 20:34:38 +00:00
Commented Jun 18, 2017 at 20:34 — pyramation
– pyramation, Commented Jun 18, 2017 at 20:34

klin · Accepted Answer · 2017-06-18 21:10:58Z

3

This query extracts ids from the jsonb objects:

select (value->>'id')::int as nested_id
from object,
jsonb_each(data->'nodes')
where id = 6;

 nested_id 
-----------
         4
         5
(2 rows)

Use it as a derived table in join:

select o.*
from object o
join (
    select (value->>'id')::int as nested_id
    from object,
    jsonb_each(data->'nodes')
    where id = 6
    ) s
on id = nested_id;

 id |                    data                     
----+---------------------------------------------
  4 | {"nodes": {"f": {"id": 1}}}
  5 | {"nodes": {"d": {"id": 2}, "e": {"id": 3}}}
(2 rows)

or as a subquery with the operator in:

select o.*
from object o
where id in (
    select (value->>'id')::int as nested_id
    from object,
    jsonb_each(data->'nodes')
    where id = 6
    );

However, this can be also done with a lateral query (as you wanted):

select s.*
from object o,
jsonb_each(data->'nodes'),
lateral (select * from object where id = (value->>'id')::int) s
where o.id = 6;

edited Jun 18, 2017 at 21:10

answered Jun 18, 2017 at 20:56

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

pyramation Over a year ago

Thanks for the response! I think it's correct, but I must be missing something very small, I took your answers and made an SQL fiddle showing the extraction is correct, however, the queries are returning 0 rows: sqlfiddle.com/#!17/6f5a3/8 - mind taking a look? could it be a bug in SQL fiddle?

klin Over a year ago

I've changed the inserted data for id = 6 in the question, according to your intent I think.See: sqlfiddle.com/#!17/39440/1

pyramation Over a year ago

Thank you, that's it! appreciate the help :)

Oto Shavadze · Accepted Answer · 2017-06-18 18:09:08Z

0

Not sure, but may be you need this:

SELECT object.id, key, value 
FROM object
JOIN LATERAL jsonb_each(data->'nodes')
ON (value->>'id')::int  = object.id
WHERE 
object.id = 6

answered Jun 18, 2017 at 18:09

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Collectives™ on Stack Overflow

query for ids inside JSONB using jsonb_each and LATERAL in postgres

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related