The 本製品 can return JSON structures as column values. The 本製品 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 |
DOCUMENT
The DOCUMENT function can be used to return an document as a JSON string. DOCUMENT(*) can be used with any type of SELECT query, including queries including other columns, queries including just DOCUMENT(*), and even more complex queries like JOINs.
SELECT [Document.Id], grade, score, DOCUMENT(*) FROM grades
For example, that query would return:
| |
Document.Id | grade | score | DOCUMENT |
1 | A | 6 | {"document.id":1,"grade":"A","score":6} |
2 | A | 10 | {"document.id":1,"grade":"A","score":10} |
3 | A | 9 | {"document.id":1,"grade":"A","score":9} |
4 | B | 14 | {"document.id":1,"grade":"B","score":14} |
When used alone, DOCUMENT(*) returns the structure directly from Couchbase as if a N1QL or SQL++ SELECT * query were used. This means that no Document.Id value will be present since Couchbase does not include it automatically.
SELECT DOCUMENT(*) FROM grades
This query would return:
| |
DOCUMENT | |
{"grades":{"grade":"A","score":6"}} | |
{"grades":{"grade":"A","score":10"}} | |
{"grades":{"grade":"A","score":9"}} | |
{"grades":{"grade":"B","score":14"}} | |