6

I want to be able to retrieve a jsonb array of objects as a record set. Maintaining the order of objects is critical. Will jsonb_to_recordset always return the record set in the order of the array of objects?

5

Here's another solution using another of Postgres' json functions: jsonb_array_elements.

  SELECT id, (value->>'benefit')::varchar as benefit, ordinality as rank
  FROM products 
  CROSS JOIN LATERAL jsonb_array_elements(benefits) with ordinality

"with ordinality" gives you the order of each array item and allows you to do an explicit order by on that field to ensure the order remains the same.

This stackoverflow post was very helpful

1
1

Will jsonb_to_recordset always return the record set in the order of the array of objects?

Maybe, maybe not — this question is based on a faulty premise, which is that a set is ordered without an explicit ORDER BY clause. Even if you could show that jsonb_to_recordset always returns the array elements in array order in every possible edge case with the version of Postgres you are using, there would be no guarantee that a future version would not change the behaviour.

In other words, even though in JSON an array is an ordered sequence of zero or more values, the output of jsonb_to_recordset is by definition unordered, no matter what you see in practice, without an explicit ORDER BY. Of course, you may consider the risk worth taking.

One (very inefficient) way of guaranteeing the order would be to use a recursive query:

create table tab(col jsonb);
insert into tab(col) values ('[{"a":"1","b":"foo"}
                              ,{"a":"2","b":"bar"}
                              ,{"a":"3","b":"baz"}]');
select * from jsonb_to_recordset((select col from tab)) as x(a int, b text);
 a | b  
-: | :--
 1 | foo
 2 | bar
 3 | baz
with recursive t(n) as ( values (0)
                         union all
                         select n+1
                         from t 
                         where n < jsonb_array_length((select col from tab))-1 )
select ((select col from tab)->n->>'a')::integer a
      ,((select col from tab)->n->>'b') b
from t 
order by n;
 a | b  
-: | :--
 1 | foo
 2 | bar
 3 | baz

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.