我有一个包含JSON数组的列的表。例如:
with example as (
select '["a", "b"]'::jsonb as col
union select ('["c", "d", "e"]'::jsonb) as col
)
select col from example
返回:
col
["a", "b"]
["c", "d", "e"]
我可以使用jsonb_array_elements
将每个数组扩展到行:
select jsonb_array_elements(col) from example
返回:
jsonb_array_elements
"a"
"b"
"c"
"d"
"e"
我想要每个数组元素的索引以及元素本身(有点像Python的enumerate
(,就像:
jsonb_array_elements array_index
"a" 1
"b" 2
"c" 1
"d" 2
"e" 3
我该怎么做?
我的应用程序只读访问,因此我无法创建函数。
使用 with ordinality
:
with example (col) as (
values
('["a", "b"]'::jsonb),
('["c", "d", "e"]'::jsonb)
)
select t.*
from example, jsonb_array_elements(col) with ordinality as t(e,idx)
返回:
e | idx
----+----
"a" | 1
"b" | 2
"c" | 1
"d" | 2
"e" | 3
with ordinality
仅在使用from
子句中的设置返回函数时才能使用,无论如何都强烈推荐。
ahh,这是一个有趣的小邮政难题。下面怎么样?
WITH example1 AS (
SELECT '["a", "b"]'::jsonb AS col
),
example2 AS (
SELECT ('["c", "d", "e"]'::jsonb) AS col
)
SELECT 1 AS group, jsonb_array_elements(col) AS jcol, row_number() OVER (ORDER BY jsonb_array_elements(col)) FROM example1
UNION
SELECT 2 AS group, jsonb_array_elements(col) AS jcol, row_number() OVER (ORDER BY jsonb_array_elements(col)) FROM example2
ORDER BY "group", row_number ASC;
会给你
1 "a" 1
1 "b" 2
2 "c" 1
2 "d" 2
2 "e" 3