php - 将 jsonb 列值转换为 PostgreSQL 中的多列

Question

假设我在 PostgreSQL 中有一个表，其中包含以下列:

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')

我想将上面的行转换成这个(在 PostgreSQL 中做一个选择):

1,"a","b",'2014/01/06'
2,"a","b",'2014/01/06'

调用 php json_encode(rows from sample)

得到这样的东西:

[{"id":1,"a":"a","b":"b","date":"2014/01/06"},
{"id":2,"a":"a","b":"b","date":"2014/01/06"}]

但现在如果我调用 php json_encode(rows from sample) 我得到这个:

[{"id":1,"jsonb":"{"a":"a","b":"b"}","date":"2014/01/06"},
{"id":2,"jsonb":"{"a":"a","b":"b"}","date":"2014/01/06"}]

希望有人能帮我处理一下，谢谢大家

Answer 1

在 9.4 中很简单(使用了 LATERAL join 和 jsonb 函数):

    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)