duanhuiuw12555 2014-06-09 04:55
浏览 616
已采纳

将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

  • 写回答

1条回答 默认 最新

  • du6333137 2014-06-09 05:21
    关注

    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)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 如何利用mmdetection3d中的get_flops.py文件计算fcos3d方法的flops?
  • ¥40 串口调试助手打开串口后,keil5的代码就停止了
  • ¥15 电脑最近经常蓝屏,求大家看看哪的问题
  • ¥60 高价有偿求java辅导。工程量较大,价格你定,联系确定辅导后将采纳你的答案。希望能给出完整详细代码,并能解释回答我关于代码的疑问疑问,代码要求如下,联系我会发文档
  • ¥50 C++五子棋AI程序编写
  • ¥30 求安卓设备利用一个typeC接口,同时实现向pc一边投屏一边上传数据的解决方案。
  • ¥15 SQL Server analysis services 服务安装失败
  • ¥15 基于面向对象的图书馆借阅管理系统
  • ¥15 opencv图像处理,需要四个处理结果图
  • ¥20 centos linux 7.9安装php8.2.18不支持mysqli模块的问题