如何在PostgreSQL中使用Lateral Join扁平化JSONB数据

需求说明 #

有如下格式的表数据：

id | json_data
 
1  | {"KEY": "mekq1232314342134434", "size": 0}
2  | {"KEY": "meksaq12323143421344", "size": 2}
3  | {"KEY": "meksaq12323324421344", "size": 3}

需要实现两个需求：

• 提取json_data字段中所有唯一的KEY值
• 将json_data扁平化，得到包含id、KEY、size的结构化结果

参考了Snowflake的Lateral Flatten写法，现需PostgreSQL的对应查询语句。

解决方案 #

1. 扁平化JSONB字段 #

PostgreSQL无需类似Snowflake的flatten函数，直接通过JSONB键访问语法即可提取对应值：

SELECT
  id,
  json_data ->> 'KEY' AS "KEY",
  (json_data ->> 'size')::INT AS size
FROM your_table_name;

执行后得到结果：

id | KEY | size
1  | mekq1232314342134434 | 0
2  | meksaq12323143421344 | 2
3  | meksaq12323324421344 | 3

• ->>操作符用于提取JSONB字段的文本值，返回TEXT类型
• size字段通过::INT转换为整数类型，适配数值场景

2. 提取唯一的KEY值 #

使用DISTINCT关键字配合JSONB键访问即可实现：

SELECT DISTINCT json_data ->> 'KEY' AS unique_key
FROM your_table_name;

如果需要去重同时保留对应id和size，可使用PostgreSQL特有的DISTINCT ON语法：

SELECT DISTINCT ON (json_data ->> 'KEY')
  id,
  json_data ->> 'KEY' AS "KEY",
  (json_data ->> 'size')::INT AS size
FROM your_table_name
ORDER BY json_data ->> 'KEY', id;

DISTINCT ON会保留每个KEY对应的第一条记录，排序规则由ORDER BY指定。

内容的提问来源于stack exchange，提问作者Siddhant Singh