PostgreSQL 15 JSON 函数增强

【再次更新】PostgreSQL 16 提供了部分 SQL/JSON 构造函数（例如 JSON_ARRAY()、JSON_ARRAYAGG() 、JSON_OBJECT()、JSON_OBJECTAGG()），以及检查函数（IS JSON）。

【更新】这些 JSON 函数增强被推迟了，最终没有包含在 PostgreSQL 15 版本中，期待后续版本。

PostgreSQL 15 Beta 1 版已经发布，正式版预计 2022 年下半年推出。新版本为开发者提供了许多新功能，例如新的 MERGE 语句、JSON 以及正则表达式函数的增强。

早在 2012 年发布的 PostgreSQL 9.2 版本就引入了 JSON 支持，而 SQL/JSON 标准比这个时间晚了五年。PostgreSQL 15 增加了更多的标准 SQL/JSON 函数，包括 SQL/JSON 构造函数、查询函数以及将 JSON 数据转换为表的能力。

SQL/JSON 构造函数

PostgreSQL 新版本增加的 SQL/JSON 构造函数包括：

• JSON()
• JSON_SCALAR()
• JSON_SERIALIZE()
• JSON_ARRAY()
• JSON_ARRAYAGG()
• JSON_OBJECT()
• JSON_OBJECTAGG()

这些函数比已有的 JSON 函数功能更强大，例如：

select json('"json"');
   json   
----------
 "json"
 
select json('[1,2,3]');
  json   
---------
 [1,2,3]

select json('{"a": 123, "b": [true, "foo"], "a": "bar"}' with unique keys);
ERROR:  duplicate JSON object key value

select json_array( 1, '2022', 'json' );
          json_array           
-------------------------------
 [1, "2022", "json"]

select json_arrayagg(datname order by datname) from pg_database;
                       json_arrayagg                       
-----------------------------------------------------------
 ["hrdb", "postgres", "template0", "template1"]

select json_object( 'id': 1, 'aid': json_array(1,2,3));
         json_object          
------------------------------
 {"id" : 1, "aid" : [1, 2, 3]}

select json_objectagg( datname: oid) from pg_database;
                                          json_objectagg                                           
---------------------------------------------------------------------------------------------------
 { "hrdb" : "16668", "postgres" : "14617", "template0" : "14616", "template1" : "1" }

其中，JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 是聚合函数，可以将数据聚合成 JSON 数组和对象。

IS JSON 谓词

IS JSON 谓词可以用于测试数据是否满足 JSON 数据格式，返回结果为 t 或者 f。具体包括：

• IS [NOT] JSON [VALUE]
• IS [NOT] JSON ARRAY
• IS [NOT] JSON OBJECT
• IS [NOT] JSON SCALAR
• IS [NOT] JSON WITH | WITHOUT UNIQUE KEYS

例如：

SELECT
    js,
    js IS JSON "is json",
    js IS NOT JSON "is not json",
    js IS JSON SCALAR "is scalar",
    js IS JSON OBJECT "is object",
    js IS JSON ARRAY "is array"
FROM
    (VALUES ('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'), ('abc')) foo(js);

     js     | is json | is not json | is scalar | is object | is array
------------+---------+-------------+-----------+-----------|-------------
 123        | t       | f           | t         | f         | f
 "abc"      | t       | f           | t         | f         | f
 {"a": "b"} | t       | f           | f         | t         | f
 [1,2]      | t       | f           | f         | f         | t
 abc        | f       | t           | f         | f         | f
(5 rows)

SQL/JSON 查询函数

SQL/JSON 查询函数用于查询 JSON 数据中的指定节点，节点通过 JSON 路径表达式指定。新版本增加的查询函数包括：

• JSON_EXISTS()
• JSON_QUERY()
• JSON_VALUE()

JSON_EXISTS() 函数用于判断指定的路径上是否存在 JSON 数据，例如：

select json_exists( '{"id":123}'::jsonb, '$.id' ), json_exists( '{"id":123}'::jsonb, '$.aid' );
 json_exists | json_exists 
-------------+-------------
 t           | f

JSON_QUERY() 函数用于返回指定路径上的 JSON 数据，例如：

select json_query('[{"x": "aaa"},{"x": "bbb"},{"x": "ccc"}]'::jsonb, '$[1].x' );
 json_query 
------------
 "bbb"

JSON_VALUE() 函数用于将指定路径上的 JSON 元素转换为标量值，例如：

SELECT JSON_VALUE('"123.45"', '$' RETURNING float);
 json_value
------------
     123.45

JSON_TABLE 函数

JSON_TABLE 函数用于将 JSON 数据转换为 SQL 表数据。这是一个很强大的转换函数，我们看一个简单的示例：

select * from json_table(
    '[{"a":10,"b":20},{"a":30,"b":40}]'::jsonb,
    '$[*]'
    COLUMNS (
        column_a int4 path '$.a',
        column_b int4 path '$.b'
    )
);
 column_a | column_b 
----------+----------
       10 |       20
       30 |       40

总结

PostgreSQL 15 增加了大量符合 SQL/JSON 标准的函数，使得 JSON 数据的处理更加简单。

PostgreSQL = SQL + NoSQL！