CODEWITHSUNDEEP
sqlarrayspostgresqljsonbset-returning-functions
user776942
user776942

Reputation:

Order by the IN value with jsonb array

I am trying to select records based on the order of array elements of another row:

SELECT * 
  FROM tester
  WHERE id IN (
    SELECT jsonb_array_elements(d->'fam')->>'id' 
    FROM tester
    WHERE id='3'
  )

I am aware of this solution.

The difference is that I don't know how to dynamically generate the "ordering" value. Is that possible?

The full fiddle is here.

I would like to see results based on the order found in the json data:

id  name    d
--  -----   --------
2   barb    {"fam": [{"id": 1}, {"id": 3}]}
4   jaimie  {"fam": [{"id": 3}, {"id": 2}, {"id": 1}]}
1   bob     {"fam": [{"id": 3}, {"id": 2}, {"id": 4}]}

Upvotes: 2

Views: 599

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657727

Use WITH ORDINALITY in a LATERAL join to preserve the original order of the array:

SELECT t.* 
FROM   tester t1
CROSS  JOIN jsonb_array_elements(t1.d->'fam') WITH ORDINALITY fam(id, ord)
JOIN   tester t ON t.id = fam.id->>'id'
WHERE  t1.id = '3'
ORDER  BY fam.ord;

SQL Fiddle.

Note a subtle difference: The IN construct in your original query not only removes original order, it also removes duplicates. My query keeps all IDs extracted from the array, duplicate or not, in original order.

The LATERAL keyword is optional noise for table functions. As is the AS keyword for table aliases. Would go here:

CROSS  JOIN LATERAL jsonb_array_elements(d->'fam') WITH ORDINALITY AS fam(id, ord)

Related:

Upvotes: 1

Related Questions