将jsonb列值转换为PostgreSQL中的多个列 [英] convert jsonb column value to multiple columns in 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屋！