PostgreSQL jsonb数组提取：将JSON列表转为含id、p列的表格

嘿，这个需求用PostgreSQL自带的jsonb处理工具就能轻松搞定！下面直接上可行的方案：

核心SQL语句 #

假设你的表名为your_table，存储JSON数组的jsonb字段名为json_data，执行以下SQL就能把数组拆分成包含id和p列的13行表格：

SELECT 
    -- 提取id字段并转为整数类型，若需保留字符串形式可去掉::INT
    (elem->>'id')::INT AS id,
    -- 提取p字段并转为整数类型
    (elem->>'p')::INT AS p
FROM 
    your_table,
    -- 将jsonb数组拆分为单个jsonb对象行
    jsonb_array_elements(your_table.json_data) AS elem;

代码解释 #

• jsonb_array_elements()：这个函数会把你的JSON数组拆分成独立的行，每一行对应数组里的一个JSON对象，我们给拆分后的对象起别名elem。
• ->>：用于提取JSON对象中指定字段的文本值，如果用->会返回jsonb类型；这里转成INT是因为你的id和p都是数字类型，方便后续的数值操作。

转换后的结果表格 #

执行上述SQL后，你会得到如下结构化表格（注意原JSON中的012是八进制写法，PostgreSQL解析后会转为十进制的12，若要保留原始字符串形式，去掉::INT即可）：

测试单个JSON数组的情况 #

如果你只是想直接测试给定的JSON字符串，不需要从表中读取，可以用下面的SQL：

SELECT 
    (elem->>'id')::INT AS id,
    (elem->>'p')::INT AS p
FROM 
    jsonb_array_elements('[ { "id": 123, "p": 1 }, { "id": 456, "p": 2 }, { "id": 789, "p": 3 }, { "id": 012, "p": 4 }, { "id": 345, "p": 5 }, { "id": 678, "p": 6 }, { "id": 901, "p": 7 }, { "id": 234, "p": 8 }, { "id": 567, "p": 9 }, { "id": 890, "p": 10 }, { "id": 1234, "p": 11 }, { "id": 5678, "p": 12 }, { "id": 9012, "p": 13 } ]'::jsonb) AS elem;

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