Determining order of elements inside json_array - SQL

Question

I have a table kind of like this, let's call it base_table:

id |    date     |     json_array
---+-------------+------------------
1    2018-01-01    [{..a..},{..b..}]
2    2018-01-01    [{..c..}]
3    2018-01-01    [{..d..}]
4    2018-01-01    [{..e..},{..f..}]
.        .                 .
.        .                 .
.        .                 .

The json_array column always has either 1 or 2 elements.

I need to make a select in SQL where on each line I can show: the respective id from base_table; its order (if the element comes 1st or 2nd inside the array); the element of the json_array.

The output should be something like this:

id | order | json_object
---+-------+------------
1     1        {..a..}
1     2        {..b..}
2     1        {..c..}
3     1        {..d..}
4     1        {..e..}
4     2        {..f..}
.     .           .
.     .           .
.     .           .

But I'm having a lot of trouble with showing the order of the elements... Can anyone help?

The database is in PostgreSQL 9.6.1

klin · Accepted Answer · 2018-05-11 13:38:41Z

5

Use json_array_elements(json_array) with ordinality:

with my_table(id, json_array) as (
values
    (1, '[{"a": 1}, {"b": 2}]'::json),
    (2, '[{"c": 3}]'),
    (3, '[{"d": 4}]'),
    (4, '[{"e": 5}, {"f": 6}]')
)

select id, ordinality, value
from my_table
cross join json_array_elements(json_array) with ordinality;

 id | ordinality |  value   
----+------------+----------
  1 |          1 | {"a": 1}
  1 |          2 | {"b": 2}
  2 |          1 | {"c": 3}
  3 |          1 | {"d": 4}
  4 |          1 | {"e": 5}
  4 |          2 | {"f": 6}
(6 rows)

DbFiddle.

Read 7.2.1.4. Table Functions in the documentation.

edited May 11, 2018 at 13:38

answered May 10, 2018 at 20:09

klin

123k15 gold badges241 silver badges263 bronze badges

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

Collectives™ on Stack Overflow

Determining order of elements inside json_array - SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related