在PostgreSQL命令行psql里格式化输出json字段

最新推荐文章于 2024-08-14 19:28:08 发布

weixin_33795806

最新推荐文章于 2024-08-14 19:28:08 发布

阅读量3k

点赞数

文章标签： json 数据库操作系统

原文链接：https://my.oschina.net/cytan/blog/542100

版权

为什么80%的码农都做不了架构师？>>>

在pgsql的psql命令里直接select输出json字段是一长串字符，这对阅读非常不友好，查了好久也没查到pgsql有格式化输出json字段的函数，只好折腾一番。

表结构如下：

pgsql=# \d test
   资料表 "pgsql.test"
 栏位 |  型别   | 修饰词 
------+---------+--------
 id   | integer | 非空
 info | jsonb   | 非空
索引：
    "test_pkey" PRIMARY KEY, btree (id)
    "test_info_idx" gin (info)

SELECT的格式化输出：

pgsql=# select id,replace(
                replace(
                        regexp_replace(info::text , ', "' , concat(',',chr(10),chr(9),'"'),'g') ,
                                '{' , concat('{',chr(10),chr(9))) ,
                '}' , concat(chr(10),'}')) as info from test;
 id |                             info                              
----+---------------------------------------------------------------
  1 | {                                                            +
    |         "a": [1, 2, 3],                                      +
    |         "b": [4, 5, 6]                                       +
    | }
  2 | {                                                            +
    |         "a": [7, 8, 9],                                      +
    |         "b": [10, 11, 12]                                    +
    | }
  3 | {                                                            +
    |         "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",      +
    |         "name": "Angela Barton",                             +
    |         "tags": ["enim",                                     +
    |         "aliquip",                                           +
    |         "qui"],                                              +
    |         "address": "178 Howard Place, Gulf, Washington, 702",+
    |         "company": "Magnafone",                              +
    |         "latitude": 19.793713,                               +
    |         "is_active": true,                                   +
    |         "longitude": 86.513373,                              +
    |         "registered": "2009-11-07T08:53:22 +08:00"           +
    | }
(3 行记录

为了方便今后每次调用，写成函数：

create function format_json(text) returns text as $$
select replace(replace(regexp_replace($1 , ', "' , concat(',',chr(10),chr(9),'"'),'g'),
'{' , concat('{',chr(10),chr(9))) ,'}' , concat(chr(10),'}')) as json_s;
$$ language sql;

这样看起来就简洁直观多了：

pgsql=# select id,format_json(info::text) as info from test where id=3;
 id |                             info                              
----+---------------------------------------------------------------
  3 | {                                                            +
    |         "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",      +
    |         "name": "Angela Barton",                             +
    |         "tags": ["enim",                                     +
    |         "aliquip",                                           +
    |         "qui"],                                              +
    |         "address": "178 Howard Place, Gulf, Washington, 702",+
    |         "company": "Magnafone",                              +
    |         "latitude": 19.793713,                               +
    |         "is_active": true,                                   +
    |         "longitude": 86.513373,                              +
    |         "registered": "2009-11-07T08:53:22 +08:00"           +
    | }
(1 行记录)

转载于:https://my.oschina.net/cytan/blog/542100