# Json函数和运算符
- Cast to JSON
  ```
  SELECT CAST(9223372036854775807 AS JSON); -- JSON '9223372036854775807'
  ```
  

- Cast from JSON
  ```
  SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
  ```
  
#### JSON函数
![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png)
NULL到JSON的转换并不能简单地实现。从独立的NULL进行转换将产生一个SQLNULL，而不是JSON 'null'。不过，在从包含NULL的数组或Map进行转换时，生成的JSON将包含NULL。
在从ROW转换为JSON时，结果是一个JSON数组，而不是一个JSON对象。这是因为对于SQL中的行，位置比名称更重要。
支持从BOOLEAN、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、DOUBLE或VARCHAR进行转换。当数组的元素类型为支持的类型之一、Map的键类型是VARCHAR且Map的值类型是支持的类型之一或行的每个字段类型是支持的类型之一时支持从ARRAY、MAP或ROW进行转换。下面通过示例展示了转换的行为：
```
SELECT CAST(NULL AS JSON);-- NULL
SELECT CAST(1 AS JSON);-- JSON '1'
SELECT CAST(9223372036854775807 AS JSON);-- JSON '9223372036854775807'
SELECT CAST('abc' AS JSON);-- JSON '"abc"'
SELECT CAST(true AS JSON);-- JSON 'true'
SELECT CAST(1.234 AS JSON);-- JSON '1.234'
SELECT CAST(ARRAY[1, 23, 456] AS JSON);-- JSON '[1,23,456]'
SELECT CAST(ARRAY[1, NULL, 456] AS JSON);-- JSON '[1,null,456]'
SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON);-- JSON '[[1,23],[456]]'
SELECT CAST(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[1, 23, 456]) AS JSON);-- JSON '{"k1":1,"k2":23,"k3":456}'
SELECT CAST(CAST(ROW(123, 'abc', true) AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON);-- JSON '[123,"abc",true]'
```
#### JSON转其他类型
```
SELECT CAST(JSON 'null' AS VARCHAR);-- NULL  
SELECT CAST(JSON '1' AS INTEGER);-- 1  
SELECT CAST(JSON '9223372036854775807' AS BIGINT);-- 9223372036854775807  
SELECT CAST(JSON '"abc"' AS VARCHAR);-- abc  
SELECT CAST(JSON 'true' AS BOOLEAN);-- true  
SELECT CAST(JSON '1.234' AS DOUBLE);-- 1.234  
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER));-- [1, 23, 456]  
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER));-- [1, NULL, 456]  
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER)));-- [[1, 23], [456]]  
SELECT CAST(JSON '{"k1":1, "k2":23, "k3":456}' AS MAP(VARCHAR, INTEGER));-- {k1=1, k2=23, k3=456}  
SELECT CAST(JSON '{"v1":123, "v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));-- {v1=123, v2=abc, v3=true}  
SELECT CAST(JSON '[123, "abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));-- {value1=123, value2=abc, value3=true}  
SELECT CAST(JSON'[[1, 23], 456]'AS ARRAY(JSON));-- [JSON '[1,23]', JSON '456']  
SELECT CAST(JSON'{"k1": [1, 23], "k2": 456}'AS MAP(VARCHAR,JSON));-- {k1 = JSON '[1,23]', k2 = JSON '456'}  
SELECT CAST(JSON'[null]'AS ARRAY(JSON));-- [JSON 'null']
```
![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png)
在从JSON转换为ROW时，支持JSON数组和JSON对象。
JSON数组可以具有混合元素类型，JSON Map可以有混合值类型。这使得在某些情况下无法将其转换为SQL数组和Map。为了解决该问题，HetuEngine支持对数组和Map进行部分转换：
```
SELECT CAST(JSON'[[1, 23], 456]'AS ARRAY(JSON));-- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON'{"k1": [1, 23], "k2": 456}'AS MAP(VARCHAR,JSON));-- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON'[null]'AS ARRAY(JSON));-- [JSON 'null']
```
- is_json_scalar(json) → boolean 判断json是否为标量（即JSON数字、JSON字符串、true、false或null）：
  ```
  select is_json_scalar(json'[1,22]'); -- false
  ```
  
- json_array_contains(json, value) → boolean 判断json中是否包含某value。
  ```
  select json_array_contains(json '[1,23,44]',23); -- true
  ```
  
- json_array_get(json_array, index) → json
  ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/notice_3.0-zh-cn.png)
  该函数的语义已被破坏。如果提取的元素是字符串，它将被转换为未正确使用引号括起来的无效JSON值（值不会被括在引号中，任何内部引号不会被转义）。建议不要使用该函数。无法在不影响现有用法的情况下修正该函数，可能会在将来的版本中删除该函数。
  返回指定索引位置的json元素，索引从0开始。
  ```
  SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
  SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
  ```
  索引页支持负数，表示从最后开始，-1表示最后一个元素，索引超过实际长度会返回null。
  ```
  SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
  SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'
  ```
  如果指定索引位置的json元素不存在，将返回NULL值。
  ```
  SELECT json_array_get('[]', 0);                -- NULL
  SELECT json_array_get('["a", "b", "c"]', 10);  -- NULL
  SELECT json_array_get('["c", "b", "a"]', -10); -- NULL
  ```
  
- json_array_length(json) → bigint 返回json的长度。
  ```
  SELECT json_array_length(json '[1,2,3,4]'); -- 4
  SELECT json_array_length('[1, 2, 3]'); -- 3
  ```
  
- get_json_object(string json,string json_path)； 按照json_path格式抓取json中的信息。
  ```
  SELECT get_json_object('{"id": 1, "value":"xxx"}', '$.value');  -- "xxx"
  ```
  
- json_extract(json, json_path) → json 按照json_path格式抓取json中的信息。
  以json_extract为例提供json_path的写法，其他场景参考json_extract即可。
  创建一个**orders**表，插入一条Json字符串：
  ```
  create table orders(order_json string);
  insert into orders values('{
        "order_id": "ORD-20260817-001",
        "customer": {
          "id": 1001,
          "name": "Alice",
          "address": {
            "city": "Shanghai",
            "district": "Pudong",
            "zip": "200120"
          },
          "tags": ["VIP", "long-term"]
        },
        "items": [
          {
            "sku": "SKU-A01",
            "product": "Laptop",
            "price": 6999.00,
            "quantity": 1,
            "specs": {"cpu": "i7-13700", "ram": "16GB", "disk": "512GB"}
          },
          {
            "sku": "SKU-B02",
            "product": "Mouse",
            "price": 199.00,
            "quantity": 2,
            "specs": {"type": "wireless", "dpi": 4000}
          },
          {
            "sku": "SKU-C03",
            "product": "Keyboard",
            "price": 399.00,
            "quantity": 1,
            "specs": {"type": "mechanical", "switches": "red"}
          }
        ],
        "payment": {
          "method": "credit_card",
          "status": "paid",
          "installments": 3
        },
        "notes": null
      }');
  ```
  各层级查询方式：
  ```
  SELECT
      -- 第一层：顶层字段
      json_extract(order_json, '$.order_id') AS order_id,
      -- 第二层：嵌套对象
      json_extract(order_json, '$.customer.name') AS customer_name,
      json_extract(order_json, '$.customer.id') AS customer_id,
      -- 第三层：深层嵌套
      json_extract(order_json, '$.customer.address.city') AS city,
      json_extract(order_json, '$.customer.address.district') AS district,
      -- 数组整体提取
      json_extract(order_json, '$.items') AS all_items,
      json_extract(order_json, '$.customer.tags') AS customer_tags,
      -- 数组按索引访问（0-based）
      json_extract(order_json, '$.items[0].product') AS first_item,
      json_extract(order_json, '$.items[1].price') AS second_item_price,
      json_extract(order_json, '$.items[2].specs.type') AS third_item_spec_type,
      -- 数组内嵌套对象
      json_extract(order_json, '$.items[0].specs.cpu') AS laptop_cpu,
      json_extract(order_json, '$.items[0].specs.ram') AS laptop_ram
  from orders;
  ```
  查询结果：
  ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/zh-cn_image_0000002683367182.png "点击放大")
  
- json_extract_scalar(json, json_path) → varchar 和json_extract功能相同，返回值是varchar。
  ```
  SELECT json_extract_scalar(json '{"id": 1, "value": "xxx"}', '$.value'); -- xxx
  ```
  
- json_format(json) → varchar 把json值转为序列化的json文本，这是json_parse的反函数：
  ```
  SELECT JSON_format(json '{"id": 1, "value":"xxx"}'); -- {"id":1, "value":"xxx"}
  ```
  注意：
  json_format和CAST(json AS VARCHAR)具有完全不同的语义。
  json_format将输入JSON值序列化为遵守7159标准的JSON文本。JSON值可以是JSON对象、JSON数组、JSON字符串、JSON数字、true、false或null：
  ```
  SELECT json_format(JSON '{"a": 1, "b": 2}'); -- '{"a":1,"b":2}' 
  SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]' 
  SELECT json_format(JSON '"abc"'); -- '"abc"' 
  SELECT json_format(JSON '42'); -- '42' 
  SELECT json_format(JSON 'true'); -- 'true' 
  SELECT json_format(JSON 'null'); -- 'null'
  ```
  CAST(json AS VARCHAR)将JSON值转换为对应的SQL VARCHAR值。对于JSON字符串、JSON数字、true、false或null，转换行为与对应的SQL类型相同。JSON对象和JSON数组无法转换为VARCHAR：
  ```
  SELECT CAST(JSON '{"a": 1, "b": 2}' AS VARCHAR); -- NULL 
  SELECT CAST(JSON '[1, 2, 3]' AS VARCHAR); -- NULL 
  SELECT CAST(JSON '"abc"' AS VARCHAR); -- 'abc'; Note the double quote is gone 
  SELECT CAST(JSON '42' AS VARCHAR); -- '42' 
  SELECT CAST(JSON 'true' AS VARCHAR); -- 'true' 
  SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
  ```
  
- json_parse(string) → json 和json_format(json)功能相反，将json格式的字符串转换为json。
  Json_parse和json_extract通常结合使用，用于解析数据表中的json字符串。
  ```
  select JSON_parse('{"id": 1, "value":"xxx"}'); -- json {"id":1, "value":"xxx"}
  ```
  
- json_size(json, json_path) → bigint 和json_extract类似，但是返回的是json里的对象个数。
  ```
  SELECT json_size('{ "x": {"a": 1, "b": 2} }', '$.x'); => 2
  SELECT json_size('{ "x": [1, 2, 3] }', '$.x'); =>3
  SELECT json_size('{ "x": {"a": 1, "b": 2} }', '$.x.a'); => 0
  ```