类别：: :doc:`/sql-reference/functions-semistructured`（解析）

PARSE_JSON¶

将输入字符串解释为 JSON 文档，并生成一个 VARIANT 值。

您有 JSON 格式的输入数据时，可以使用 PARSE_JSON 函数。此函数可以将数据从 JSON 格式设置为 ARRAY 或 OBJECT 数据并将该数据直接存储在 VARIANT 值中。然后，您可以分析或操作数据。

默认情况下，该函数不允许 JSON 对象中的重复键，但您可以将 'parameter' 实参设为允许重复键。

另请参阅：: TRY_PARSE_JSON

语法¶

PARSE_JSON( <expr> [ , '<parameter>' ] )

Copy

实参¶

必填：

expr: 字符串类型的表达式（例如 VARCHAR），该类型可保存有效的 JSON 信息。

可选：

'parameter'

字符串常量，指定用于搜索匹配项的参数。支持的值：

参数	描述
`d`	允许 JSON 对象中的重复键。如果 JSON 对象包含重复键，则返回的对象具有该键的单个实例，其中包含为该键指定的最后一个值。
`s`	不允许 JSON 对象（严格）中的重复键。此值为默认值。

返回¶

返回值为 VARIANT 类型，包含 JSON 文档。

如果输入为 NULL，则该函数返回 NULL。

此函数不返回结构化类型。

使用说明¶

此函数支持最大压缩大小为 8 MB 的输入表达式。
如果使用空字符串或仅包含空格字符的字符串调用 PARSE_JSON 函数，则该函数返回 NULL（而不是引发错误），即使空字符串不是有效的 JSON。此行为允许继续进行处理，而不是在某些输入为空字符串时中止。
如果输入为 NULL，则输出也是 NULL。但是，如果输入字符串为 'null'，那么它将被解释为 JSON null 值，因此结果不是 SQL NULL，而是包含 null 的有效 VARIANT 值。请参阅以下示例。
解析十进制数时，PARSE_JSON 试图通过将 123.45 视为 NUMBER(5,2) 而非 DOUBLE 值，从而保持表示的准确性。但是，使用科学记数法（例如 1.2345e+02）的数字或由于范围或比例限制而无法存储为定点小数的数字存储为 DOUBLE 值。因为 JSON 本身不表示 TIMESTAMP、DATE、TIME 或 BINARY 等值，所以这些值必须表示为字符串。
在 JSON 中，对象（也称为“字典”或“哈希”）是一组无序的键值对。

TO_JSON 和 PARSE_JSON（几乎）是相反或互逆的函数。
- PARSE_JSON 函数接受一个字符串作为输入，并返回一个 JSON 兼容的 VARIANT。
- TO_JSON 函数接受一个 JSON 兼容的 VARIANT 并返回一个字符串。
如果 X 是包含有效 JSON 的字符串，则以下内容（从概念上讲）为真：

X = TO_JSON(PARSE_JSON(X));

例如，以下情况（从概念上讲）为真：

'{"pi":3.14,"e":2.71}' = TO_JSON(PARSE_JSON('{"pi":3.14,"e":2.71}'))

但是，这些函数并不完全互逆，因为：
- 空字符串和只有空格的字符串不会互逆。例如，PARSE_JSON('') 的返回值为 NULL，但 TO_JSON(NULL) 的返回值为 NULL，而不是倒数 ''。
- TO_JSON 生成的字符串中键值对的顺序是不可预测的。
- TO_JSON 生成的字符串可以比传递给 PARSE_JSON 的字符串具有更少的空格。
例如，以下是等效的 JSON，但不是等效的字符串：
- {"pi": 3.14, "e": 2.71}
- {"e":2.71,"pi":3.14}

示例¶

以下示例使用 PARSE_JSON 函数。

将不同数据类型的值存储在 VARIANT 列¶

此示例通过调用 PARSE_JSON 存储 VARIANT 列中的不同数据类型，来解析字符串。

创建并填写表。INSERT 语句使用 PARSE_JSON 函数将 VARIANT 值插入表的 v 列。

CREATE OR REPLACE TABLE vartab (n NUMBER(2), v VARIANT);

INSERT INTO vartab
  SELECT column1 AS n, PARSE_JSON(column2) AS v
    FROM VALUES (1, 'null'), 
                (2, null), 
                (3, 'true'),
                (4, '-17'), 
                (5, '123.12'), 
                (6, '1.912e2'),
                (7, '"Om ara pa ca na dhih"  '), 
                (8, '[-1, 12, 289, 2188, false,]'), 
                (9, '{ "x" : "abc", "y" : false, "z": 10} ') 
       AS vals;

Copy

查询数据。查询使用 TYPEOF 函数显示存储在 VARIANT 值中的数据类型。

SELECT n, v, TYPEOF(v)
  FROM vartab
  ORDER BY n;

Copy

+---+------------------------+------------+
| N | V                      | TYPEOF(V)  |
|---+------------------------+------------|
| 1 | null                   | NULL_VALUE |
| 2 | NULL                   | NULL       |
| 3 | true                   | BOOLEAN    |
| 4 | -17                    | INTEGER    |
| 5 | 123.12                 | DECIMAL    |
| 6 | 1.912000000000000e+02  | DOUBLE     |
| 7 | "Om ara pa ca na dhih" | VARCHAR    |
| 8 | [                      | ARRAY      |
|   |   -1,                  |            |
|   |   12,                  |            |
|   |   289,                 |            |
|   |   2188,                |            |
|   |   false,               |            |
|   |   undefined            |            |
|   | ]                      |            |
| 9 | {                      | OBJECT     |
|   |   "x": "abc",          |            |
|   |   "y": false,          |            |
|   |   "z": 10              |            |
|   | }                      |            |
+---+------------------------+------------+

在 VARIANT 中插入具有重复键的 JSON 对象¶

尝试在 VARIANT 值中插入具有重复键的 JSON 对象：

INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2) AS v
  FROM VALUES (10, '{ "a" : "123", "b" : "456", "a": "789"} ')
     AS vals;

Copy

返回错误，因为默认情况下不允许重复键：

100069 (22P02): Error parsing JSON: duplicate object attribute "a", pos 31

在 VARIANT 值中插入具有重复键的 JSON 对象对象，并指定 d 参数以允许重复项：

INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2, 'd') AS v
  FROM VALUES (10, '{ "a" : "123", "b" : "456", "a": "789"} ')
     AS vals;

Copy

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

对表的查询显示仅插入了最后一个重复键的值：

SELECT v
  FROM vartab
  WHERE n = 10;

Copy

+---------------+
| V             |
|---------------|
| {             |
|   "a": "789", |
|   "b": "456"  |
| }             |
+---------------+

使用 PARSE_JSON 和 TO_JSON 函数处理 NULL 值¶

以下示例显示了 PARSE_JSON 和 TO_JSON 如何处理 NULL 值：

SELECT TO_JSON(NULL), TO_JSON('null'::VARIANT),
       PARSE_JSON(NULL), PARSE_JSON('null');

Copy

+---------------+--------------------------+------------------+--------------------+
| TO_JSON(NULL) | TO_JSON('NULL'::VARIANT) | PARSE_JSON(NULL) | PARSE_JSON('NULL') |
|---------------+--------------------------+------------------+--------------------|
| NULL          | "null"                   | NULL             | null               |
+---------------+--------------------------+------------------+--------------------+

比较 PARSE_JSON 和 TO_JSON¶

以下示例演示了 PARSE_JSON 和 TO_JSON 函数之间的关系。

此示例创建了一个具有 VARCHAR 列和 VARIANT 列的表。INSERT 语句插入一个 VARCHAR 值，且 UPDATE 语句生成一个与该 VARCHAR 值对应的 JSON 值。

CREATE OR REPLACE TABLE jdemo2 (
  varchar1 VARCHAR, 
  variant1 VARIANT);

INSERT INTO jdemo2 (varchar1) VALUES ('{"PI":3.14}');

UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);

Copy

该查询表明 TO_JSON 和 PARSE_JSON 在概念上是互逆函数：

SELECT varchar1, 
       PARSE_JSON(varchar1), 
       variant1, 
       TO_JSON(variant1),
       PARSE_JSON(varchar1) = variant1, 
       TO_JSON(variant1) = varchar1
  FROM jdemo2;

Copy

+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+
| VARCHAR1    | PARSE_JSON(VARCHAR1) | VARIANT1     | TO_JSON(VARIANT1) | PARSE_JSON(VARCHAR1) = VARIANT1 | TO_JSON(VARIANT1) = VARCHAR1 |
|-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------|
| {"PI":3.14} | {                    | {            | {"PI":3.14}       | True                            | True                         |
|             |   "PI": 3.14         |   "PI": 3.14 |                   |                                 |                              |
|             | }                    | }            |                   |                                 |                              |
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+

但是，这些函数并不完全是互逆的。键值对的空格或顺序差异可能会阻止输出与输入匹配。例如：

SELECT TO_JSON(PARSE_JSON('{"b":1,"a":2}')),
       TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"b":1,"a":2}',
       TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"a":2,"b":1}';

Copy

+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
| TO_JSON(PARSE_JSON('{"B":1,"A":2}')) | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"B":1,"A":2}' | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"A":2,"B":1}' |
|--------------------------------------+--------------------------------------------------------+--------------------------------------------------------|
| {"a":2,"b":1}                        | False                                                  | True                                                   |
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+

比较 PARSE_JSON 和 TO_VARIANT¶

尽管 PARSE_JSON 函数和 TO_VARIANT 函数可以接受一个字符串并返回一个 VARIANT 值，它们不是等效的。以下示例创建一个包含两个 VARIANT 列的表。然后，它使用 PARSE_JSON 将值插入到一列中，使用 TO_VARIANT 将值插入到另一列中。

CREATE OR REPLACE TABLE jdemo3 (
  variant1 VARIANT,
  variant2 VARIANT);

INSERT INTO jdemo3 (variant1, variant2)
  SELECT
    PARSE_JSON('{"PI":3.14}'),
    TO_VARIANT('{"PI":3.14}');

Copy

下面的查询显示，函数返回了 VARIANT 值，该值存储不同数据类型的值。

SELECT variant1,
       TYPEOF(variant1),
       variant2,
       TYPEOF(variant2),
       variant1 = variant2
  FROM jdemo3;

Copy

+--------------+------------------+-----------------+------------------+---------------------+
| VARIANT1     | TYPEOF(VARIANT1) | VARIANT2        | TYPEOF(VARIANT2) | VARIANT1 = VARIANT2 |
|--------------+------------------+-----------------+------------------+---------------------|
| {            | OBJECT           | "{\"PI\":3.14}" | VARCHAR          | False               |
|   "PI": 3.14 |                  |                 |                  |                     |
| }            |                  |                 |                  |                     |
+--------------+------------------+-----------------+------------------+---------------------+