JSON Data Type

JSON data type

Overview

JSON (JavaScript Object Notation) is a text-based data format that is widely used as a data exchange format. JSON can intuitively express data structures and provides the flexibility to input various types of data. The maximum size of the JSON data type supported by Altibase is 2 GB (2,147,483,648 bytes).

Altibase follows the following JSON standards.

• Standards related to JSON definition follow the definition of RFC 8259.
• Standards related to JSON path expressions follow the definition of ISO/IEC 19075-6 (2021).
• JSON function-related standards follow the definition of ISO/IEC 19075-6 (2021).

JSON

Flow chart

in_row_clause ::=

Syntax

JSON [ IN ROW size ]

Description

Provides the JSON data type so that you can efficiently store and retrieve JSON documents. JSON documents are stored as objects (key-value pairs) or arrays (ordered lists of values). For a detailed explanation of the IN ROW clause, refer to the previously described "IN ROW clause".

Constraints

• Same as restrictions for LOB type columns.
• The maximum depth of a JSON document is 256.
• Temporary LOB is used when processing JSON documents. Therefore, to use the JSON type, the TEMPORARY_LOB_ENABLE property must be set to 1.

JSON functions

The JSON functions supported by Altibase are as follows. For detailed information on each function, refer to JSON Functions in SQL Reference manual.

• JSON document creation function -JSON_ARRAY -JSON_OBJECT
• JSON document search function -JSON_EXISTS -JSON_QUERY -JSON_VALUE
• JSON document verification function -JSON_VALID

JSON conditional operators

It provides conditional operators of IS JSON and IS NOT JSON, and is an operator that checks whether a JSON document satisfies the JSON format or not. For a detailed explanation, refer to IS JSON in SQL Reference.

JSON Path Expression

A JSON path expression sets conditions to use for search to extract a specific value from a JSON document. Altibase follows the JSON path expression standard ISO/IEC 19075-6. The components of path expressions supported by Altibase are as follows.

• path symbol
• Dot (.) operator
• Square bracket operator
• Wild Card
• Filter expression

path expression	Description
path sign	There is a root node and a current node. The root node is expressed as '$' and represents the starting point of the JSON document. When searching in a JSON document, search starting from the root node. The current node is expressed as '@' and indicates the location of the JSON document currently being accessed by the operator.
dot (.) operator	The dot operator is expressed as '.' and is used when accessing a specific key of a JSON object.
Square bracket operator	The bracket operator is expressed as '[]' and is used when accessing elements of a JSON array. Numbers and wildcards can be used within square bracket operators.
Wildcard	The wildcard operator is expressed as '*' and is used with the dot operator to access the keys of all objects, or with the bracket operator to access all elements of an array.
filter expression	The filter expression is '? It is expressed as ‘(logical-expr)’ and is used when extracting data that meets the conditions in parentheses.

An example of extracting results from a JSON document using a JSON path expression is as follows.

• JSON document

  {
    "name": "Hong Gildong",
    "age": 22,
    "class": [
      "Mathematics",
      "Science"
    ],
    "address": {
      "city": "Seoul",
      "country": "Korea"
    }
  }
  

• JSON path expressions and results

  JSON path expression	Results
  '$'	{"name":"Hong Gildong", "age":22, "class":["Mathmatics", "Science"], "address":{"city":"Seoul", "country":"Korea" }}
  '$.name'	"Hong Gildong"
  '$.address.city'	"Seoul"
  '$.class[0]'	"Mathematics"
  '$.class[1]'	"Science"
  '$.class[*]'	["Mathematics", "Science"]
  '$.age?(@>20)'	22
  '$.age?(@<20)'	-

JSON path expression constraints

There are the following restrictions when using path expressions in the JSON function provided by Altibase.

• Path expressions can only be used in string form.
• Bind variables, NULL, table columns, SQL functions, user-defined functions, etc. cannot be used.