我正在使用两个JSONB数组，将它们解压，并对结果进行梳理。我试图在数组中添加 WITH ORDINALITY 到JSON数组解包。我一直想不明白如何在JSON数组中加入 WITH ORDINALITY. 不知为什么，我找不到 WITH ORDINALITY Postgres 11的JSON工具文档中。
https:/www.postgresql.orgdocs11functions-json.html
我见过使用 jsonb_array_elements....WITH ORDINALITY但一直没能成功。首先，是一个基于Postgres数组的功能示例。
WITH
first AS (
SELECT * FROM
UNNEST (ARRAY['Charles','Jane','George','Percy']) WITH ORDINALITY AS x(name_, index)
),
last AS (
SELECT * FROM
UNNEST (ARRAY['Dickens','Austen','Eliot']) WITH ORDINALITY AS y(name_, index)
)
SELECT first.name_ AS first_name,
last.name_ AS last_name
FROM first
JOIN last ON (last.index = first.index)
这样就能得到想要的结果
first_name last_name
Charles Dickens
Jane Austen
George Eliot
我使用的是 ORDINALITY 指数 JOIN因为我把两个名单合并起来进行配对比较。我可以假设我的列表大小相同。
然而，我的输入是一个JSON数组，而不是Postgres数组。我已经在解包过程中使用了 jsonb_to_recordset但还没有让序数生成工作。这里有一个正确完成解包部分的例子。
DROP FUNCTION IF EXISTS tools.try_ordinality (jsonb, jsonb);
CREATE OR REPLACE FUNCTION tools.try_ordinality (
base_jsonb_in jsonb,
comparison_jsonb_in jsonb)
RETURNS TABLE (
base_text citext,
base_id citext,
comparison_text citext,
comparison_id citext)
AS $BODY$
BEGIN
RETURN QUERY
WITH
base_expanded AS (
select *
from jsonb_to_recordset (
base_jsonb_in)
AS base_unpacked (text citext, id citext)
),
comparison_expanded AS (
select *
from jsonb_to_recordset (
comparison_jsonb_in)
AS comparison_unpacked (text citext, id citext)
),
combined_lists AS (
select base_expanded.text AS base_text,
base_expanded.id AS base_id,
comparison_expanded.text AS comparison_text,
comparison_expanded.id AS comparison_id
from base_expanded,
comparison_expanded
)
select *
from combined_lists;
END
$BODY$
LANGUAGE plpgsql;
select * from try_ordinality (
'[
{"text":"Fuzzy Green Bunny","id":"1"},
{"text":"Small Gray Turtle","id":"2"}
]',
'[
{"text":"Red Large Special","id":"3"},
{"text":"Blue Small","id":"4"},
{"text":"Green Medium Special","id":"5"}
]'
);
但这是一个 CROSS JOIN
base_text base_id comparison_text comparison_id
Fuzzy Green Bunny 1 Red Large Special 3
Fuzzy Green Bunny 1 Blue Small 4
Fuzzy Green Bunny 1 Green Medium Special 5
Small Gray Turtle 2 Red Large Special 3
Small Gray Turtle 2 Blue Small 4
Small Gray Turtle 2 Green Medium Special 5
我追求的是只有两行的配对结果。
Fuzzy Green Bunny 1 Red Large Special 3
Small Gray Turtle 2 Blue Small 4
我试过换成 jsonb_array_elements，就像这个片段一样。
WITH
base_expanded AS (
select *
from jsonb_array_elements (
base_jsonb_in)
AS base_unpacked (text citext, id citext)
),
我回来了
ERROR: a column definition list is only allowed for functions returning "record"
有没有一种直接的方法来获取一个未打包的JSON数组的序数？很简单，用 UNNEST 在一个Postgres数组上。
我很高兴知道我把语法搞乱了。
我可以 CREATE TYPE如果有帮助的话
我可以转换为Postgres数组，如果这样做很直接的话。
谢谢大家的建议。
1
投票
投票
你的做法完全一样。
with first as (
select *
from jsonb_array_elements('[
{"text":"Fuzzy Green Bunny","id":"1"},
{"text":"Small Gray Turtle","id":"2"}
]'::jsonb) with ordinality as f(element, idx)
), last as (
select *
from jsonb_array_elements('[
{"text":"Red Large Special","id":"3"},
{"text":"Blue Small","id":"4"},
{"text":"Green Medium Special","id":"5"}
]'::jsonb) with ordinality as f(element, idx)
)
SELECT first.element ->> 'text' AS first_name,
last.element ->> 'text' AS last_name
FROM first
JOIN last ON last.idx = first.idx
