时间戳

首页 > 解决方案 > 将多种数据类型的数组拆分为不同的数组

json - 将多种数据类型的数组拆分为不同的数组

问题描述

我在 postgres 数据库中有可用的数据，我需要在这些数据库上运行 BI 工具来获取一些聚合信息。不幸的是，数据以最奇怪的方式结构化。我有示例 json 列可以使用：

{"products": ["345661C1-2665-4870-9649-803B20A4B579", 1, "FE719978-253F-4763-B1B7-648B9988C5BF", 2, "F66FE491-AC06-49DD-987B-0B88CB49CEB7", 2, "5628A5A4-6030-459D-96F3-32D3C04B7F80", 3, "2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C", 1]}

我确实意识到 products 数组本身不是正确的 postgres 语法，字符串文字也不是。

理想情况下，我有一个查询导致：

产品 数量
345661C1-2665-4870-9649-803B20A4B579 1
FE719978-253F-4763-B1B7-648B9988C5BF 2
F66FE491-AC06-49DD-987B-0B88CB49CEB7 2
5628A5A4-6030-459D-96F3-32D3C04B7F80 3
2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C 1

这对postgres可行吗？我在寻找

我总是会寻找带有代码的解决方案，但 BI 工具是该数据库唯一列入白名单的工具

标签： jsonpostgresql

解决方案

这感觉有点骇人听闻（如果您的数组不是“键/值”列表格式，则会失败），但您可以使用它WITH ORDINALITY来跟踪每个元素的索引并分组ordinality / 2（将连续的元素重新组合在一起）：

架构 (PostgreSQL v13)

查询 #1

WITH data(arr) AS (
  VALUES (ARRAY['345661C1-2665-4870-9649-803B20A4B579', '1', 'FE719978-253F-4763-B1B7-648B9988C5BF', '2', 'F66FE491-AC06-49DD-987B-0B88CB49CEB7', '2', '5628A5A4-6030-459D-96F3-32D3C04B7F80', '3', '2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C', '1'])
)
SELECT (ARRAY_AGG(unnest ORDER BY ordinality))[1] AS product, (ARRAY_AGG(unnest ORDER BY ordinality))[2] AS amount
FROM data
CROSS JOIN LATERAL UNNEST(arr) WITH ORDINALITY
GROUP BY (ordinality-1) / 2; /* ordinality is 1-based, hence the -1 */
产品 数量
345661C1-2665-4870-9649-803B20A4B579 1
FE719978-253F-4763-B1B7-648B9988C5BF 2
F66FE491-AC06-49DD-987B-0B88CB49CEB7 2
5628A5A4-6030-459D-96F3-32D3C04B7F80 3
2B8DAE11-5D60-4DB7-901B-0CCBA7D9418C 1

在 DB Fiddle 上查看

推荐阅读