全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 钉钉智能硬件
流计算

JSON函数

更新时间:2017-06-07 13:26:11

GET_JSON_OBJECT

函数定义:

  1. string get_json_object(string json, string path)

用途:在一个标准json字符串中,按照path抽取指定的字符串。

参数说明:

  • json:String类型,标准的json格式字符串。

  • path:String类型,用于描述在json中的path,以$开头。

返回值:String类型

注解:

  • 如果json为空或者非法的json格式,返回NULL

  • 如果path为空或者不合法(json中不存在)返回NULL

  • 如果json合法,path也存在则返回对应字符串

示例一

  1. +----+
  2. json
  3. +----+
  4. {"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"bicycle":{"price":19.95,"color":"red"}},"email":"amy@only_for_json_udf_test.net","owner":"amy"}

以下语句可以提取json对象中的信息:

  1. SELECT get_json_object(t.json, '$.owner') FROM src_json t;
  2. amy
  3. SELECT get_json_object(t.json, '$.store.fruit\[0]') FROM src_json t;
  4. {"weight":8,"type":"apple"}
  5. SELECT get_json_object(t.json, '$.non_exist_key') FROM src_json t;
  6. NULL

示例二

  1. get_json_object('{"array":[[aaaa,1111],[bbbb,2222],[cccc,3333]]}','$.array[1].[1]') = "2222"
  2. get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]') = "["h0","h1","h2"]"
  3. get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]') = "h1"

JSON_TUPLE

函数定义:

  1. string json_tuple(string json,string key1, string key2, ...)

用途:一次获取json中key1、key2…对应的多个对象,该功能可以显著提高效率。

参数说明:

  • json:String类型,标准的json格式字符串。

  • key1/2:String类型,用于描述在json中对象的key,不需要加$标示符读取对象。

返回值:String元组

示例一

  1. +----+
  2. json
  3. +----+
  4. {"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"bicycle":{"price":19.95,"color":"red"}},"email":"amy@only_for_json_udf_test.net","owner":"amy"}

示范语句:

  1. insert into
  2. result_table
  3. select
  4. json_tuple(t.a, 'store','owner')
  5. from
  6. src_json t;

结果:

  1. {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"bicycle":{"price":19.95,"color":"red"}} amy
本文导读目录