JSON 查询语法

本文旨在指导用户如何利用 SQL 语法高效地查询 JSON 类型的数据。通过掌握这些查询方法，用户能够简化查询流程。JSON 类型的创建请参考文档 JSON类型。

语法

--键名访问JSON对象中的字段 json_column['key']['key']... --通过索引访问JSON数组中的元素 json_array[index]

参数说明

```
json_column
```
json_column
：表示一个 JSON 字段，其类型为 JSON 对象。通过键名（
```
key
```
key
，指定为字符串）来定位并检索 JSON 对象内的特定数据字段。使用单个方括号
```
[]
```
[]
可以访问对象的一级字段；而嵌套使用双方括号
```
[][]
```
[][]
则能够深入检索对象的二级或更深层次的字段。
```
json_array
```
json_array
：表示数组类型的 JSON 字段，用于根据索引（index）访问 JSON 数组的元素，索引起始值为 0。

返回值说明

返回值为 JSON 类型。如需其他类型，可使用 CAST 函数进行强制转换。类型转换请参考 JSON类型文档。

案例

取出JSON一级结构

SELECT parse_json(s)['firstName'] as j FROM VALUES ('{ "firstName": "John", "lastName": "doe", "age": 26, "address": { "streetAddress": "naist street", "city": "Nara", "postalCode": "630-0192" }, "phoneNumbers": [ { "type": "iPhone", "number": "0123-4567-8888" }, { "type": "home", "number": "0123-4567-8910", "extra": [] } ] }'), ('{ "firstName": "Ada", "lastName": "doe", "age": 20, "address": { "streetAddress": "naist street", "city": "Nara", "postalCode": "630-0192" }, "address2": {"city": "NewYork"}, "phoneNumbers": [ { "type": "iPhone", "number": "0123-4567-8888" }, { "type": "home", "number": "0123-4567-8910", "extra": [1,2,3] } ] }') as t(s);

+--------+ | j | +--------+ | "John" | | "Ada" | +--------+

返回值为 JSON 类型，字符串值带双引号。如需去掉引号，使用

::string

::string

转换：

parse_json(s)['firstName']::string

parse_json(s)['firstName']::string

。

取出JSON二级结构

SELECT parse_json(s)['address']['streetAddress'] as j FROM VALUES ('{ "firstName": "John", "lastName": "doe", "age": 26, "address": { "streetAddress": "naist street", "city": "Nara", "postalCode": "630-0192" }, "phoneNumbers": [ { "type": "iPhone", "number": "0123-4567-8888" }, { "type": "home", "number": "0123-4567-8910", "extra": [] } ] }'), ('{ "firstName": "Ada", "lastName": "doe", "age": 20, "address": { "streetAddress": "naist street", "city": "Nara", "postalCode": "630-0192" }, "address2": {"city": "NewYork"}, "phoneNumbers": [ { "type": "iPhone", "number": "0123-4567-8888" }, { "type": "home", "number": "0123-4567-8910", "extra": [1,2,3] } ] }') as t(s);

+----------------+ | j | +----------------+ | "naist street" | | "naist street" | +----------------+

取出JSON数组中的元素

SELECT parse_json(s)['phoneNumbers'][0]['number'] as j FROM VALUES ('{ "firstName": "John", "lastName": "doe", "age": 26, "address": { "streetAddress": "naist street", "city": "Nara", "postalCode": "630-0192" }, "phoneNumbers": [ { "type": "iPhone", "number": "0123-4567-8888" }, { "type": "home", "number": "0123-4567-8910", "extra": [] } ] }'), ('{ "firstName": "Ada", "lastName": "doe", "age": 20, "address": { "streetAddress": "naist street", "city": "Nara", "postalCode": "630-0192" }, "address2": {"city": "NewYork"}, "phoneNumbers": [ { "type": "iPhone", "number": "0123-4567-8888" }, { "type": "home", "number": "0123-4567-8910", "extra": [1,2,3] } ] }') as t(s);

+------------------+ | j | +------------------+ | "0123-4567-8888" | | "0123-4567-8888" | +------------------+

联系我们