Introduction to GBase 8c JSON/JSONB Data Type Syntax

What Types of Data Do JSON/JSONB Support?
JSON (JavaScript Object Notation) data can be a scalar, an array, or a key-value object. Arrays and objects are collectively referred to as containers:

• Scalar: A single number, boolean, string, or null can be called a scalar.
• Array: An array is enclosed in [], and its elements can be any type of JSON. The elements do not need to be of the same type.
• Object: An object is enclosed in {} and stores key-value pairs. The keys must be strings wrapped in double quotes "", and the values can be any type of JSON. For duplicate keys, the last key-value pair will take precedence.

GBase 8c database supports two related data types: JSON and JSONB, both of which can store JSON data. The JSON type stores a complete copy of the input string, preserving spaces, duplicate keys, and order. On the other hand, JSONB stores the data in a parsed binary format, removing irrelevant details like extra spaces and duplicate keys. It also sorts key-value pairs, making it more efficient to process without requiring further parsing.

Both JSON and JSONB accept the same input strings. The main difference between them is efficiency:

• JSON stores an exact copy of the input text, which needs to be re-parsed every time a function is executed.
• JSONB stores the data in a parsed binary format, which makes input slightly slower due to the conversion process but significantly speeds up processing since it avoids repeated parsing.

JSONB also supports indexing, which is a significant advantage.

Input Format

The input must be a string in valid JSON format, enclosed in single quotes (' ').

Null (null-json):

Only null (in lowercase) is accepted.

SELECT 'null'::json;   -- success
SELECT 'NULL'::jsonb;  -- error

Number (num-json):

Positive and negative integers, decimals, and zero are supported. Scientific notation is also accepted.

SELECT '1'::json;
SELECT '-1.5'::json;
SELECT '-1.5e-5'::jsonb, '-1.5e+2'::jsonb;

However, leading zeros, the plus sign (+) for positive numbers, and special values like NaN and infinity are not supported.

SELECT '001'::json, '+15'::json, 'NaN'::json; -- not supported

Boolean (bool-json):

Only true and false in lowercase are supported.

SELECT 'true'::json;
SELECT 'false'::jsonb;  

String (str-json):

Must be enclosed in double quotes.

SELECT '"a"'::json;
SELECT '"abc"'::jsonb;

Array (array-json):

Enclosed in square brackets ([]). The elements can be any valid JSON type, and the types do not need to be consistent.

SELECT '[1, 2, "foo", null]'::json;
SELECT '[]'::json;
SELECT '[1, 2, "foo", null, [[]], {}]'::jsonb; 

Object (object-json):

Enclosed in curly braces ({}). Keys must be valid JSON strings, and values can be any valid JSON.

SELECT '{}'::json;
SELECT '{"a": 1, "b": {"a": 2, "b": null}}'::json;
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::jsonb;

Note:

• 'nul'::json and null::json are two different concepts, similar to an empty string ("") versus null.
• For numbers in scientific notation, the jsonb type will expand them, while json will store the exact input.

JSONB Advanced Features

The main difference between JSON and JSONB is in the storage format. JSONB stores a parsed binary representation of the JSON, which reflects its hierarchical structure, making it easier to access and operate on directly. As a result, JSONB has several advanced features that JSON does not. Note that JSONB does not support column storage, partition keys, external tables, or memory tables.

1) Normalization

For input object-json strings, the jsonb type discards unnecessary details such as extra spaces.

SELECT '   [1, " a ", {"a"   :1    }]  '::jsonb; 
-- Result: [1, " a ", {"a": 1}]

It also removes duplicate keys, keeping only the last one:

SELECT '{"a" : 1, "a" : 2}'::jsonb; 
-- Result: {"a": 2}

Key-value pairs are re-sorted based on length (longer ones appear later) and, for equal-length keys, by ASCII value:

SELECT '{"aa" : 1, "b" : 2, "a" : 3}'::jsonb; 
-- Result: {"a": 3, "b": 2, "aa": 1}

2) Comparison

Due to normalization, jsonb guarantees that there is only one representation for the same semantic meaning, allowing for size comparison. The comparison order is:
object-jsonb > array-jsonb > bool-jsonb > num-jsonb > str-jsonb > null-jsonb

Within the same type, values are compared as follows:

• str-json: Compared based on text using the default database collation.
• num-json: Compared numerically.
• bool-json: true is greater than false.
• array-jsonb: Longer arrays are greater; if equal in length, elements are compared one by one.
• object-jsonb: Longer objects are greater; if equal in length, key-value pairs are compared by key first, then by value.

3) Creating Indexes, Primary Keys, and Foreign Keys

• BTREE Index: jsonb supports creating btree indexes, as well as primary and foreign keys.
• GIN Index: GIN indexes efficiently search for keys or key-value pairs in large jsonb documents. It provides two operator classes (jsonb_ops and jsonb_hash_ops) with different performance trade-offs.

4) Containment and Existence

jsonb allows for querying whether certain elements are contained in a JSON structure or whether certain elements exist in a JSON.

• Scalar values only contain the same value:

SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

• Arrays on the left can contain strings from the right:

SELECT '[1, "aa", 3]'::jsonb ? 'aa';

• Arrays contain all elements from the right, regardless of order or repetition:

SELECT '[1, 2, 3]'::jsonb @> '[1, 3, 1]'::jsonb;

• Objects contain all key-value pairs from the right:

SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb":true}'::jsonb @> '{"version":9.4}'::jsonb;

• Arrays do not contain elements if their structures differ:

SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;

This article gives a brief overview of the JSON/JSONB data types supported by GBase 8c, but there may be more methods to explore. Thank you for reading!