The add-in can return JSON structures as column values. The add-in 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"}} | |