PostgreSQL jsonb_populate_record() 函数使用指南

PostgreSQL jsonb_populate_record() 函数将指定的最顶层的 JSONB 对象转为一个自定义的 SQL 类型值。

jsonb_populate_record() 语法

这是 PostgreSQL jsonb_populate_record() 函数的语法：

jsonb_populate_record(base ANYELEMENT, from_json JSONB) -> ANYELEMENT

参数

base
必需的。 一个任何数据类型的值。 它指明了 JSONB 对象要转为的值的类型。
from_json
必需的。 要转的 JSONB 对象。

返回值

PostgreSQL jsonb_populate_record() 函数返回一个自定义的 SQL 类型值，它由指定的 JSONB 对象转化而来。

在 JSONB 对象的那些与自定义类型中的列名匹配的字段，他们的值将被插入到对应的输出的列中。而 JSONB 对象中的那些没有匹配到自定类型中的列名的字段将会被忽略。

jsonb_populate_record() 函数按照如下的规则的将 JSONB 值转为 SQL 类型的值：

  1. JSONB null 将被转为 SQL null。
  2. 如果输出列的类型为 JSONB 或者 JSONB，JSON 值将被精确的复制。
  3. 如果输出列是符合类型，并且 JSONB 值是对象，则通过递归应用这些规则，将对象的字段转换为输出行类型的列。
  4. 如果输出列是数组类型并且 JSONB 值是 JSONB 数组，则通过递归应用这些规则，将 JSONB 数组的元素转换为输出数组的元素。
  5. 否则，如果 JSONB 值是字符串，则将字符串的内容将被送到该列数据类型对应的输入转换函数。
  6. 否则，JSON 值的普通文本表示将被送到该列数据类型的输入转换函数。

在典型使用中，base 的值为 NULL，这意味着任何不与 JSONB 对象中的字段匹配的输出列都将用空值填充。 如果 base 的值不为 NULL，则它包含的值将被用于不匹配的列中。

通常，我们可以使用 CREATE TYPE 创建一个自定义类型，它定义了要输出的列以及每个列的类型。

jsonb_populate_record() 示例

本示例展示了如何使用 PostgreSQL jsonb_populate_record() 函数将一个 JSONB 对象转为一个自定义 SQL 类型的值。

首先，让我们创建两个自定义的 SQL 类型：

CREATE TYPE address as (country TEXT, city TEXT);
CREATE TYPE person as (name TEXT, age INT, hobbies TEXT[], address address);

这里，我们创建了两个自定类型 address 和 person，其中 person 内部使用了 address。

然后，让我们使用下面的语句将 JSONB 对象转为刚刚创建的类型 person：

SELECT
  *
FROM
  jsonb_populate_record(
    null::person,
    '{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"], "address": {"country": "CN", "city": "BeiJing"}}'
  );

 name | age |    hobbies    |   address
------+-----+---------------+--------------
 Tom  |  20 | {sports,cars} | (CN,BeiJing)

我们可以使用一个不为 NULL 的 base 参数，以便未输出中的未匹配的列填充一个值，比如：

SELECT
  *
FROM
  jsonb_populate_record(
    ('x', 0, ARRAY['sports'], ('CN', 'BeiJing'))::person,
    '{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"]}'
  );

 name | age |    hobbies    |   address
------+-----+---------------+--------------
 Tom  |  20 | {sports,cars} | (CN,BeiJing)

这里，我们的 base 参数为 ('x', 0, ARRAY['sports'], ('CN', 'BeiJing'))::person，其中 address 列的值为 ('CN', 'BeiJing')。 虽然给定的 JSONB 对象中没有 address 字段，但是输出中的列 address 被 base 参数中的值填充。