sql - 确定 json_array 中元素的顺序 - SQL

Question

我有一个类似这样的表，我们称它为 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..}]
.        .                 .
.        .                 .
.        .                 .

json_array 列总是有 1 个或 2 个元素。

我需要在 SQL 中进行选择，其中每一行我都可以显示:来自 base_table 的相应 ID；它的顺序(如果元素在数组中排在第 1 位或第 2 位)； json_array 的元素。

输出应该是这样的:

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

但是我在显示元素的顺序方面遇到了很多麻烦...有人可以帮忙吗？

数据库是PostgreSQL 9.6.1

Answer 1

使用带序数的json_array_elements(json_array):

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.

阅读 7.2.1.4。 the documentation. 中的表函数