pg中多值列,将jsonb列值转换为PostgreSQL中的多个列

lets say i have a table in PostgreSQL with the following columns:

CREATE TABLE sample

(

id int,

jsonb jsonb,

date date

)

and i inserted this two rows:

INSERT INTO sample

(id,jsonb,date)

VALUES

(1, '{"a":"a","b":"b"}', '2014/01/06'),

(2, '{"a":"a","b":"b"}', '2014/01/06')

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'

to call in php json_encode(rows from sample)

and get something like this:

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

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

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

解决方案

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)

exact result:

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)