JDBC Driver for Couchbase

Build 22.0.8462

JSON Functions

The driver can return JSON structures as column values. The driver 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 NameExample Value
GradeA
Score2

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 NameExample Value
NumberOfGrades5

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 NameExample 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 NameExample Value
LowestScore2

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 NameExample Value
HighestScore14

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"}}

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462