Presto | JSON 与支持的数据类型之间相互转换

Presto 支持的各种数据类型的值如何转换为 JSON 格式？

Presto 支持将如下基本数据类型的值转换为 JSON 格式：

BOOLEAN、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、DOUBLE、VARCHAR。

此外，Presto 同样支持将满足以下条件的数组（ARRAY）、映射（MAP）或行（ROW）转换为 JSON 格式：

• 数组的元素类型须为上述某一受支持的数据类型；
• 映射的键类型是 VARCHAR 且值类型须为上述某一受支持的数据类型；
• 行的每个字段类型均须为上述某一受支持的数据类型。

示例

例 1 NULL 将换为 JSON：

SELECT CAST(NULL AS JSON);

 _col0
-------
 NULL
(1 row)

例 2 INTEGER 将换为 JSON：

SELECT CAST(1 AS JSON);

 _col0
-------
 1
(1 row)

例 3 BIGINT 将换为 JSON：

SELECT CAST(9223372036854775807 AS JSON);

        _col0
---------------------
 9223372036854775807
(1 row)

例 4 VARCHAR 将换为 JSON：

SELECT CAST('abc' AS JSON);

 _col0
-------
 "abc"
(1 row)

例 5 BOOLEAN 将换为 JSON：

SELECT CAST(true AS JSON);

 _col0
-------
 true
(1 row)

例 6 DECIMAL 将换为 JSON：

SELECT CAST(1.234 AS JSON);

 _col0
-------
 1.234
(1 row)

例 7 元素为整数的 ARRAY 将换为 JSON：

SELECT CAST(ARRAY[1, 23, 456] AS JSON);

   _col0
------------
 [1,23,456]
(1 row)

例 8 包含 NULL 元素的整数 ARRAY 将换为 JSON：

SELECT CAST(ARRAY[1, NULL, 456] AS JSON);

    _col0
--------------
 [1,null,456]
(1 row)

例 9 元素为整数数组的 ARRAY 将换为 JSON：

SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON);

     _col0
----------------
 [[1,23],[456]]
(1 row)

例 10 MAP 将换为 JSON：

SELECT CAST(MAP_FROM_ENTRIES(ARRAY[('k1', 1), ('k2', 23), ('k3', 456)]) AS JSON);

           _col0
---------------------------
 {"k1":1,"k2":23,"k3":456}
(1 row)

例 11 ROW 将换为 JSON：

SELECT CAST(CAST(ROW(123, 'abc', true) AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON);

      _col0
------------------
 [123,"abc",true]
(1 row)

注意

在将 SQL 的 NULL 值转换成 JSON 格式时，并不只是简单地将其转换为 SQL 的 NULL 或 JSON 的 null。

如果一个字段的值恰好是 NULL，转换后的 JSON 结果会是一个 SQL NULL，而不是 JSON 的 null。但是，如果是一个包含 NULL 元素的数组或映射转换成 JSON，结果中的 NULL 会被保留为 null。

例如：

• 包含 NULL 的数组 [1, 2, NULL, 4] 转换成 JSON 结果为 [1, 2, null, 4]
• 一个键对应的值是 NULL 的映射 {“key1”: 1, “key2”: NULL} 转换成 JSON 的结果为 {“key1”: 1, “key2”: null}

也就是说，一个单独的 NULL 字段转换成 JSON 时，结果会是一个 SQL NULL。但如果是一个结构类型（如数组或映射）包含 NULL 转换成 JSON，NULL 值会被保留成 null。

另外，还有一个需要注意的点是，当从 SQL ROW 转换为 JSON 时，结果是一个 JSON 数组，而不是一个 JSON 对象。这是因为在 SQL 中，ROW 的位置比其名称更重要。

从 JSON 转换

Presto 支持将 JSON 数据转换为绝大部分内置数据类型，以及由这些类型构成的 ARRAY、MAP 和 ROW 等复杂数据类型。这极大地便利了 JSON 数据的处理和分析。

具体而言，Presto 支持将 JSON 数据转换为以下基本类型：

BOOLEAN、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、DOUBLE、VARCHAR。

Presto 也支持将满足以下条件的 JSON 数组和对象的数据转换为 ARRAY 和 MAP 类型：

• JSON 数组中的元素类型为上述基本类型之一，转换为 ARRAY 类型；
• JSON 对象的键类型为 VARCHAR 且值类型为上述基本类型之一，转换为 MAP 类型。

示例

例 1 JSON null 值转 VARCHAR：

SELECT CAST(JSON 'null' AS VARCHAR);

 _col0
-------
 NULL
(1 row)

例 2 JSON 字符串值转 INTEGER：

SELECT CAST(JSON '1' AS INTEGER);

 _col0
-------
     1
(1 row)

例 3 JSON 长整数值转 BIGINT：

SELECT CAST(JSON '9223372036854775807' AS BIGINT);

        _col0
---------------------
 9223372036854775807
(1 row)

例 4 JSON 字符串值转 VARCHAR：

SELECT CAST(JSON '"abc"' AS VARCHAR);

 _col0
-------
 abc
(1 row)

例 5 JSON 布尔值转 BOOLEAN：

SELECT CAST(JSON 'true' AS BOOLEAN);

 _col0
-------
 true
(1 row)

例 6 JSON 小数值转 DOUBLE 类型：

SELECT CAST(JSON '1.234' AS DOUBLE);

 _col0
-------
 1.234
(1 row)

例 7 JSON 整数数组转 ARRAY(INTEGER) 类型：

SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER));

    _col0
--------------
 [1, 23, 456]
(1 row)

例 8 包含元素为 null 值的 JSON 整数数组转 ARRAY(INTEGER) 类型：

SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER));

     _col0
----------------
 [1, null, 456]
(1 row)

例 9 元素为整数数组的 JSON 数组转 ARRAY(ARRAY(INTEGER)) 类型：

SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER)));

      _col0
------------------
 [[1, 23], [456]]
(1 row)

例 10 JSON 对象转 MAP 类型：

SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER));

         _col0
-----------------------
 {k1=1, k2=23, k3=456}
(1 row)

例 11 JSON 对象转 ROW 类型：

SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));

           _col0
---------------------------
 {v1=123, v2=abc, v3=true}
(1 row)

例 12 JSON 数组转 ROW 类型：

SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));

           _col0
---------------------------
 {v1=123, v2=abc, v3=true}
(1 row)

注意

JSON 数组可以包含多种类型的元素，JSON 映射的值也可以是多种类型。这使得在某些情况下无法直接将它们转换为 SQL 数组和映射。为解决这个问题，Presto 支持数组和映射的部分转换。例如：

例 13 转成元素为 JSON 的数组：

SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON));

     _col0
---------------
 [[1,23], 456]
(1 row)

例 14 转成值类型为 JSON 的映射：

SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON));

        _col0
---------------------
 {k1=[1,23], k2=456}
(1 row)

例 15 转成元素为 JSON 的数组：

SELECT CAST(JSON '[null]' AS ARRAY(JSON));

 _col0
--------
 [null]
(1 row)

另外，Presto 也支持将 JSON 数组和 JSON 对象转换为 ROW 类型，如：

例 16 JSON 数组转换为 ROW 类型：

SELECT CAST(JSON '[1, 2, 3]' AS ROW(x int, y int, z int));

      _col0
-----------------
 {x=1, y=2, z=3}
(1 row)

例 17 JSON 对象转换为 ROW 类型：

SELECT CAST(JSON '{"x": 1, "y": 2}' AS ROW(x int, y int));

   _col0
------------
 {x=1, y=2}
(1 row)

（END）