JSON Functions
The adapter can return JSON structures as column values. The adapter enables you to use standard SQL functions to work with these JSON structures. The examples in this section use the following array:
[
{ "grade": "A", "score": 2 },
{ "grade": "A", "score": 6 },
{ "grade": "A", "score": 10 },
{ "grade": "A", "score": 9 },
{ "grade": "B", "score": 14 }
]
JSON_EXTRACT
The JSON_EXTRACT function can extract individual values from a JSON object. The following query returns the values shown below based on the JSON path passed as the second argument to the function:
SELECT Name, JSON_EXTRACT(grades,'[0].grade') AS Grade, JSON_EXTRACT(grades,'[0].score') AS Score FROM Students;
Column Name | Example Value |
Grade | A |
Score | 2 |
JSON_COUNT
The JSON_COUNT function returns the number of elements in a JSON array within a JSON object. The following query returns the number of elements specified by the JSON path passed as the second argument to the function:
SELECT Name, JSON_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;
Column Name | Example Value |
NumberOfGrades | 5 |
JSON_SUM
The JSON_SUM function returns the sum of the numeric values of a JSON array within a JSON object. The following query returns the total of the values specified by the JSON path passed as the second argument to the function:
SELECT Name, JSON_SUM(score,'[x].score') AS TotalScore FROM Students;
Column Name | Example Value |
TotalScore | 41 |
JSON_MIN
The JSON_MIN function returns the lowest numeric value of a JSON array within a JSON object. The following query returns the minimum value specified by the JSON path passed as the second argument to the function:
SELECT Name, JSON_MIN(score,'[x].score') AS LowestScore FROM Students;
Column Name | Example Value |
LowestScore | 2 |
JSON_MAX
The JSON_MAX function returns the highest numeric value of a JSON array within a JSON object. The following query returns the maximum value specified by the JSON path passed as the second argument to the function:
SELECT Name, JSON_MAX(score,'[x].score') AS HighestScore FROM Students;
Column Name | Example Value |
HighestScore | 14 |