- Release Notes and Announcements
- User Guide
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Resource Management
- Permission Management
- Log Collection
- Collection Overview
- Collecting Logs in Self-Built Kubernetes Cluster
- Collecting Syslog
- Collection by LogListener
- Collecting Text Log
- Uploading Log over Kafka
- Uploading Logs via Anonymous Write
- Uploading Logs via Logback Appender
- Uploading Logs via Log4j Appender
- Uploading Log via SDK
- Uploading Log via API
- Importing Data
- Tencent Cloud Service Log Access
- Metric Collection
- Log Storage
- Metric Storage
- Search and Analysis (Log Topic)
- Syntax and Rules
- Statistical Analysis (SQL)
- Quick Analysis
- SQL Syntax
- SQL Functions
- String Function
- Date and Time Functions
- IP Geographic Function
- URL Function
- Mathematical Calculation Functions
- Mathematical Statistical Function
- General Aggregate Function
- Geospatial Function
- Binary String Function
- Estimation Function
- Type Conversion Function
- Logical Function
- Operators
- Bitwise Operation
- Regular Expression Function
- Lambda Function
- Conditional Expressions
- Array Functions
- Interval-Valued Comparison and Periodicity-Valued Comparison Functions
- JSON Functions
- Window Functions
- Sampling Analysis
- Configuring Indexes
- Reindexing
- Context Search and Analysis
- Downloading Log
- Search and Analysis (Metric Topic)
- Dashboard
- Data Processing documents
- Data Processing
- Creating Processing Task
- Viewing Data Processing Details
- Data Processing Functions
- Function Overview
- Key-Value Extraction Functions
- Enrichment Functions
- Flow Control
- Row Processing Functions
- Field Processing Functions
- Value Structuring Functions
- Regular Expression Processing Functions
- Time Value Processing Functions
- String Processing Functions
- Type Conversion Functions
- Logical and Mathematical Functions
- Encoding and Decoding Functions
- IP Parsing Functions
- Processing Cases
- Scheduled SQL Analysis
- SCF
- Data Processing
- Shipping and Consumption
- Monitoring Alarm
- Historical Documentation
- Practical Tutorial
- Developer Guide
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Topic Management APIs
- Log Set Management APIs
- Index APIs
- Topic Partition APIs
- Machine Group APIs
- Collection Configuration APIs
- Log APIs
- Metric APIs
- Alarm Policy APIs
- Data Processing APIs
- Kafka Protocol Consumption APIs
- CKafka Shipping Task APIs
- Kafka Data Subscription APIs
- COS Shipping Task APIs
- SCF Delivery Task APIs
- Scheduled SQL Analysis APIs
- COS Data Import Task APIs
- Data Types
- Error Codes
- FAQs
- CLS Service Level Agreement
- CLS Policy
- Contact Us
- Glossary
- Release Notes and Announcements
- User Guide
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Resource Management
- Permission Management
- Log Collection
- Collection Overview
- Collecting Logs in Self-Built Kubernetes Cluster
- Collecting Syslog
- Collection by LogListener
- Collecting Text Log
- Uploading Log over Kafka
- Uploading Logs via Anonymous Write
- Uploading Logs via Logback Appender
- Uploading Logs via Log4j Appender
- Uploading Log via SDK
- Uploading Log via API
- Importing Data
- Tencent Cloud Service Log Access
- Metric Collection
- Log Storage
- Metric Storage
- Search and Analysis (Log Topic)
- Syntax and Rules
- Statistical Analysis (SQL)
- Quick Analysis
- SQL Syntax
- SQL Functions
- String Function
- Date and Time Functions
- IP Geographic Function
- URL Function
- Mathematical Calculation Functions
- Mathematical Statistical Function
- General Aggregate Function
- Geospatial Function
- Binary String Function
- Estimation Function
- Type Conversion Function
- Logical Function
- Operators
- Bitwise Operation
- Regular Expression Function
- Lambda Function
- Conditional Expressions
- Array Functions
- Interval-Valued Comparison and Periodicity-Valued Comparison Functions
- JSON Functions
- Window Functions
- Sampling Analysis
- Configuring Indexes
- Reindexing
- Context Search and Analysis
- Downloading Log
- Search and Analysis (Metric Topic)
- Dashboard
- Data Processing documents
- Data Processing
- Creating Processing Task
- Viewing Data Processing Details
- Data Processing Functions
- Function Overview
- Key-Value Extraction Functions
- Enrichment Functions
- Flow Control
- Row Processing Functions
- Field Processing Functions
- Value Structuring Functions
- Regular Expression Processing Functions
- Time Value Processing Functions
- String Processing Functions
- Type Conversion Functions
- Logical and Mathematical Functions
- Encoding and Decoding Functions
- IP Parsing Functions
- Processing Cases
- Scheduled SQL Analysis
- SCF
- Data Processing
- Shipping and Consumption
- Monitoring Alarm
- Historical Documentation
- Practical Tutorial
- Developer Guide
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Topic Management APIs
- Log Set Management APIs
- Index APIs
- Topic Partition APIs
- Machine Group APIs
- Collection Configuration APIs
- Log APIs
- Metric APIs
- Alarm Policy APIs
- Data Processing APIs
- Kafka Protocol Consumption APIs
- CKafka Shipping Task APIs
- Kafka Data Subscription APIs
- COS Shipping Task APIs
- SCF Delivery Task APIs
- Scheduled SQL Analysis APIs
- COS Data Import Task APIs
- Data Types
- Error Codes
- FAQs
- CLS Service Level Agreement
- CLS Policy
- Contact Us
- Glossary
This document introduces the basic syntax and examples of JSON functions.
Function | Syntax | Description |
json_array_contains(x, value) | Determines whether a JSON array contains a given value. | |
json_array_get(x, index) | Returns the element with the specified index in a given JSON array. | |
json_array_length(x) | Returns the number of elements in a given JSON array. If `x` is not a JSON array, `null` will be returned. | |
json_extract(x, json_path) | Extracts a set of JSON values (array or object) from a JSON object or array. | |
json_extract_scalar(x, json_path) | Extracts a set of scalar values (strings, integers, or Boolean values) from a JSON object or array. Similar to the `json_extract` function. | |
json_format(x) | Converts a JSON value into a string value. | |
json_parse(x) | Converts a string value into a JSON value. | |
json_size(x, json_path) | Calculates the number of elements in a JSON object or array. |
json_array_contains
The
json_array_contains function is used to determine whether a JSON array contains a specified value.Syntax
json_array_contains(x, value)
Parameter description
Parameter | Description |
x | The parameter value is a JSON array. |
value | Value. |
Return value type
Boolean
Example
Determine whether the JSON array [1, 2, 3] contains 2.
Search and analysis statement
* | SELECT json_array_contains('[1, 2, 3]', 2)
Search and analysis result
TRUE
json_array_get
The
json_array_get function is used to get the element with a specified index in a JSON array.Syntax
json_array_get(x, index)
Parameter description
Parameter | Description |
x | The parameter value is a JSON array. |
index | JSON subscript (index), starting from 0. |
Return value type
Varchar
Example
Return the element with index 1 in the JSON array ["a", [3, 9], "c"].
Search and analysis statement
* | SELECT json_array_get('["a", [3, 9], "c"]', 1)
Search and analysis result
[3,9]
json_array_length
The
json_array_length function is used to calculate the number of elements in a JSON array. If x is not a JSON array, null will be returned.Syntax
json_array_length(x)
Parameter description
Parameter | Description |
x | The parameter value is a JSON array. |
Return value type
Bigint
Example
Example 1. Calculate the number of JSON elements in the apple.message field
apple.message:[{"traceName":"StoreMonitor"},{"topicName":"persistent://apache/pulsar/test-partition-17"},{"producerName":"pulsar-mini-338-36"},{"localAddr":"pulsar://pulsar-mini-broker-5.pulsar-mini-broker.pulsar.svc.cluster.local:6650"},{"sequenceId":826},{"storeTime":1635905306062},{"messageId":"19422-24519"},{"status":"SUCCESS"}]
Search and analysis statement
* | SELECT json_array_length(apple.message)
Search and analysis result
8
json_extract
The
json_extract function is used to extract a set of JSON values (array or object) from a JSON object or array.Syntax
json_extract(x, json_path)
Parameter description
Parameter | Description |
x | The parameter value is a JSON object or array. |
json_path | Note: JSON syntax requiring array element traversal is not supported, such as the following: $.store.book[*].author, $..book[(@.length-1)], $..book[?(@.price<10)]. |
Return value type
JSON string
Example
Get the value of epochSecond in the apple.instant field.
Field sample
apple.instant:{"epochSecond":1635905306,"nanoOfSecond":63001000}
Search and analysis statement
* | SELECT json_extract(apple.instant, '$.epochSecond')
Search and analysis result
1635905306
json_extract_scalar
The
json_extract_scalar function is used to extract a set of scalar values (strings, integers, or Boolean values) from a JSON object or array.Syntax
json_extract_scalar(x, json_path)
Parameter description
Parameter | Description |
x | The parameter value is a JSON array. |
json_path | Note: JSON syntax requiring array element traversal is not supported, such as the following: $.store.book[*].author, $..book[(@.length-1)], $..book[?(@.price<10)]. |
Return value type
Varchar
Example
Get the value of epochSecond from the apple.instant field and convert the value into a bigint value for summation.
Field sample
apple.instant:{"epochSecond":1635905306,"nanoOfSecond":63001000}
Search and analysis statement
* | SELECT sum(cast(json_extract_scalar(apple.instant,'$.epochSecond') AS bigint) )
Search and analysis result
1635905306
json_format
The
json_format function is used to convert a JSON value into a string value.Syntax
json_format(x)
Parameter description
Parameter | Description |
x | The parameter value is of JSON type. |
Return value type
Varchar
Example
Convert the JSON array [1,2,3] into a string [1, 2, 3].
Search and analysis statement
* | SELECT json_format(json_parse('[1, 2, 3]'))
Search and analysis result
[1, 2, 3]
json_parse
The
json_parse function is used to convert a string value into a JSON value and determine whether it complies with the JSON format.Syntax
json_parse(x)
Parameter description
Parameter | Description |
x | The parameter value is a string. |
Return value type
JSON
Example
Convert the JSON array [1,2,3] into a string [1, 2, 3].
Search and analysis statement
* | SELECT json_parse('[1, 2, 3]')
Search and analysis result
[1, 2, 3]
json_size
The
json_size function is used to calculate the number of elements in a JSON object or array.Syntax
json_size(x, json_path)
Parameter description
Parameter | Description |
x | The parameter value is a JSON object or array. |
json_path | JSON path, in the format of $.store.book[0].title. |
Return value type
Bigint
Example
Convert the JSON array [1,2,3] into a string [1, 2, 3].
Search and analysis statement
* | SELECT json_size(json_parse('[1, 2, 3]'),'$')
Search and analysis result
3
Yes
No
Was this page helpful?