Order by the IN value with jsonb array

Question

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}]}

What should be generating the ordering in your expected result set? The JSON data in your demo already seems to have the ordering you want. — Tim Biegeleisen, Oct 30 '17 at 02:02

Erwin Brandstetter · Accepted Answer · 2017-11-11T01:31:14.903

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)

Order by the IN value with jsonb array

1 Answers1