将jsonb列值转换为PostgreSQL中的多个列 [英] convert jsonb column value to multiple columns in PostgreSQL
本文介绍了将jsonb列值转换为PostgreSQL中的多个列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
让我说我在PostgreSQL中有一个带有以下列的表:
lets say i have a table in PostgreSQL with the following columns:
CREATE TABLE sample
(
id int,
jsonb jsonb,
date date
)
并且我插入了这两行:
INSERT INTO sample
(id,jsonb,date)
VALUES
(1, '{"a":"a","b":"b"}', '2014/01/06'),
(2, '{"a":"a","b":"b"}', '2014/01/06')
我想将上面的行转换为this(在PostgreSQL中进行选择):
i want to convert the above rows into this(doing a select in PostgreSQL):
1,"a","b",'2014/01/06'
2,"a","b",'2014/01/06'
调用php json_encode(示例行)
并获取像这样的东西:
[{"id":1,"a":"a","b":"b","date":"2014/01/06"},
{"id":2,"a":"a","b":"b","date":"2014/01/06"}]
但是现在,如果我在php中调用 json_encode(rows从示例)
我得到这个:
but now if i call in php json_encode(rows from sample)
i get this:
[{"id":1,"jsonb":"{"a":"a","b":"b"}","date":"2014/01/06"},
{"id":2,"jsonb":"{"a":"a","b":"b"}","date":"2014/01/06"}]
希望大家能帮助我解决这个问题
hope someone can help me to handle that, thanks to everyone
推荐答案
在9.4中很简单(使用了LATERAL join和jsonb函数):
It is simple in 9.4 (used LATERAL join and jsonb functions):
postgres=# SELECT *
FROM sample, jsonb_to_record(jsonb, true) AS x(a text, b text);
id | jsonb | date | a | b
----+------------------------------+-------------+------+--------
1 | {"a": "a", "b": "b"} | 2014-01-06 | a | b
2 | {"a": "a", "b": "b"} | 2014-01-06 | a | b
3 | {"a": "Ahoj", "b": "Nazdar"} | 2014-01-06 | Ahoj | Nazdar
(3 rows)
精确结果:
postgres=# SELECT id, a, b, date
FROM sample, jsonb_to_record(jsonb, true) AS x(a text, b text);
id | a | b | date
----+------+--------+------------
1 | a | b | 2014-01-06
2 | a | b | 2014-01-06
3 | Ahoj | Nazdar | 2014-01-06
(3 rows)
这篇关于将jsonb列值转换为PostgreSQL中的多个列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文