Postgres:将JSON列扩展为行 [英] Postgres: Expand JSON column into rows
问题描述
在Postgres 9.3数据库中,我有一个表,其中一列包含JSON,如下面示例中所示的测试表所示.
In a Postgres 9.3 database I have a table in which one column contains JSON, as in the test table shown in the example below.
test=# create table things (id serial PRIMARY KEY, details json, other_field text);
CREATE TABLE
test=# \d things
Table "public.things"
Column | Type | Modifiers
-------------+---------+-----------------------------------------------------
id | integer | not null default nextval('things_id_seq'::regclass)
details | json |
other_field | text |
Indexes:
"things_pkey" PRIMARY KEY, btree (id)
test=# insert into things (details, other_field)
values ('[{"json1": 123, "json2": 456},{"json1": 124, "json2": 457}]', 'nonsense');
INSERT 0 1
test=# insert into things (details, other_field)
values ('[{"json1": 234, "json2": 567}]', 'piffle');
INSERT 0 1
test=# select * from things;
id | details | other_field
----+-------------------------------------------------------------+-------------
1 | [{"json1": 123, "json2": 456},{"json1": 124, "json2": 457}] | nonsense
2 | [{"json1": 234, "json2": 567}] | piffle
(2 rows)
JSON始终是包含可变数量哈希值的数组.每个哈希始终具有相同的键集.我正在尝试编写一个查询,该查询为JSON数组中的每个条目返回一行,并为每个哈希键和Things表中的ID设置列.我希望输出如下所示:
The JSON is always an array containing a variable number of hashes. Each hash always has the same set of keys. I am trying to write a query which returns a row for each entry in the JSON array, with columns for each hash key and the id from the things table. I'm hoping for output like the following:
thing_id | json1 | json2
----------+-------+-------
1 | 123 | 456
1 | 124 | 457
2 | 234 | 567
即两行用于JSON数组中包含两项的条目.是否可以让Postgres做到这一点?
json_populate_recordset
似乎是答案的重要组成部分,但我无法让它一次处理多行.
i.e. two rows for entries with two items in the JSON array. Is it possible to get Postgres to do this?
json_populate_recordset
feels like an essential part of the answer, but I can't get it to work with more than one row at once.
推荐答案
select id,
(details ->> 'json1')::int as json1,
(details ->> 'json2')::int as json2
from (
select id, json_array_elements(details) as details
from things
) s
;
id | json1 | json2
----+-------+-------
1 | 123 | 456
1 | 124 | 457
2 | 234 | 567
这篇关于Postgres:将JSON列扩展为行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!