有一个表，其中 pars_col 列包含不同结构的 json 字符串。第一行类型：{'values': [{'test': 'A', 'values': [{'value': 8, 'test': 'B'}]}]}，第二行类型：{'values': [{'value': 21.0, 'test': 'D'}]}。一行可以包含多个在测试中含义不同、名称不同的值。我需要提取每一行的所有值并将它们相加。所有这些都需要保存在一列中。
代码有几个错误，感谢@ValNik，他的评论使它变得更好。还剩下一个问题，我的代码没有从数组双重嵌套的行中提取所有值。
例如表格中包含一行：{"values": [{"test": "A", "values": [{"value": 2, "test": "B"}]}, {"test": "D", "values": [{"value": 3, "test": "E"}]}]}'::TEXT)，代码只会从中提取2，但是需要提取2和3并将它们相加。
我的代码：
-- временная таблица для примера
CREATE TEMP VIEW df AS
SELECT
prg_id,
name,
dt,
pars_col::json as pars_col
FROM
(
SELECT
prg_id,
name,
dt,
pars_col
FROM
(VALUES
(1, 'Product A', '2023-01-01'::DATE, '{"values": [{"test": "A", "values": [{"value": 8, "test": "B"}]}]}'::TEXT),
(2, 'Product B', '2023-02-01'::DATE, '{"values": [{"value": 21.0, "test": "D"}]}'::TEXT),
(23, 'Product C', '2023-01-01'::DATE, '{"values": [{"test": "A", "values": [{"value": 5, "test": "B"}]}, {"value": 10, "test": "D"}]}'::TEXT),
(245, 'Product D', '2023-02-01'::DATE, '{"values": [{"test": "A", "values": [{"value": 2, "test": "B"}]}, {"test": "D", "values": [{"value": 3, "test": "E"}]}]}'::TEXT),
(32, 'Product E', '2023-01-01'::DATE, '{"values": [{"value": 15, "test": "F"}]}'::TEXT),
(31, 'Product G', '2023-02-01'::DATE, '{"values": [{"test": "G", "values": [{"value": 7, "test": "H"}]}]}'::TEXT)
) AS data(prg_id, name, dt, pars_col)
) as sub;
-- запрос, с помощью которого я пытаюсь распарсить строки в колонке pars_col
SELECT
prg_id,
name,
dt,
sum(combined.value)
FROM df
CROSS JOIN LATERAL (
SELECT
SUM((elem ->> 'value')::numeric) AS value
FROM
pg_catalog.json_array_elements(pars_col -> 'values') AS elem
UNION
SELECT
SUM((elem2 ->> 'value')::numeric) AS value
FROM
pg_catalog.json_array_elements((pars_col -> 'values') -> 0 -> 'values') AS elem2
) AS combined
GROUP BY prg_id, name, dt
输出为：
该表显示 prg_id 245 未完全提取值。请帮我修复错误。
