Working with JSON Data

Greenplum Database supports the json and jsonb data types that store JSON (JavaScript Object Notation) data.

Greenplum Database supports JSON as specified in the RFC 7159 document and enforces data validity according to the JSON rules. There are also JSON-specific functions and operators available for the json and jsonb data types. See JSON Functions and Operators.

This section contains the following topics:

• About JSON Data
• JSON Input and Output Syntax
• Designing JSON documents
• jsonb Containment and Existence
• jsonb Indexing
• JSON Functions and Operators

Parent topic: Querying Data

About JSON Data

Greenplum Database supports two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major difference is one of efficiency.

• The json data type stores an exact copy of the input text. This requires JSON processing functions to reparse json data on each execution. The json data type does not alter the input text.
  • Semantically-insignificant white space between tokens is retained, as well as the order of keys within JSON objects.
  • All key/value pairs are kept even if a JSON object contains duplicate keys. For duplicate keys, JSON processing functions consider the last value as the operative one.

• The jsonb data type stores a decomposed binary format of the input text. The conversion overhead makes data input slightly slower than the json data type. However, The JSON processing functions are significantly faster because reparsing jsonb data is not required. The jsonb data type alters the input text.

  • White space is not preserved.
  • The order of object keys is not preserved.
  • Duplicate object keys are not kept. If the input includes duplicate keys, only the last value is kept.

  The jsonb data type supports indexing. See jsonb Indexing.

In general, JSON data should be stored as the jsonb data type unless there are specialized needs, such as legacy assumptions about ordering of object keys.

About Unicode Characters in JSON Data

The RFC 7159 document permits JSON strings to contain Unicode escape sequences denoted by \uXXXX. However, Greenplum Database allows only one character set encoding per database. It is not possible for the json data type to conform rigidly to the JSON specification unless the database encoding is UTF8. Attempts to include characters that cannot be represented in the database encoding will fail. Characters that can be represented in the database encoding, but not in UTF8, are allowed.

• The Greenplum Database input function for the json data type allows Unicode escapes regardless of the database encoding and checks Unicode escapes only for syntactic correctness (a \u followed by four hex digits).
• The Greenplum Database input function for the jsonb data type is more strict. It does not allow Unicode escapes for non-ASCII characters (those above U+007F) unless the database encoding is UTF8. It also rejects \u0000, which cannot be represented in the Greenplum Database text type, and it requires that any use of Unicode surrogate pairs to designate characters outside the Unicode Basic Multilingual Plane be correct. Valid Unicode escapes, except for \u0000, are converted to the equivalent ASCII or UTF8 character for storage; this includes folding surrogate pairs into a single character.

Note: Many of the JSON processing functions described in JSON Functions and Operators convert Unicode escapes to regular characters. The functions throw an error for characters that cannot be represented in the database encoding. You should avoid mixing Unicode escapes in JSON with a non-UTF8 database encoding, if possible.

Mapping JSON Data Types to Greenplum Data Types

When converting JSON text input into jsonb data, the primitive data types described by RFC 7159 are effectively mapped onto native Greenplum Database data types, as shown in the following table.

There are some minor constraints on what constitutes valid jsonb data that do not apply to the json data type, nor to JSON in the abstract, corresponding to limits on what can be represented by the underlying data type. Notably, when converting data to the jsonb data type, numbers that are outside the range of the Greenplum Database numeric data type are rejected, while the json data type does not reject such numbers.

Such implementation-defined restrictions are permitted by RFC 7159. However, in practice such problems might occur in other implementations, as it is common to represent the JSON number primitive type as IEEE 754 double precision floating point (which RFC 7159 explicitly anticipates and allows for).

When using JSON as an interchange format with other systems, be aware of the possibility of losing numeric precision compared to data originally stored by Greenplum Database.

Also, as noted in the previous table, there are some minor restrictions on the input format of JSON primitive types that do not apply to the corresponding Greenplum Database data types.

JSON Input and Output Syntax

The input and output syntax for the json data type is as specified in RFC 7159.

The following are all valid json expressions:

-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::json;
-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::json;
-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- Arrays and objects can be nested arbitrarily
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

As previously stated, when a JSON value is input and then printed without any additional processing, the json data type outputs the same text that was input, while the jsonb data type does not preserve semantically-insignificant details such as whitespace. For example, note the differences here:

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

One semantically-insignificant detail worth noting is that with the jsonb data type, numbers will be printed according to the behavior of the underlying numeric type. In practice, this means that numbers entered with E notation will be printed without it, for example:

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

However, the jsonb data type preserves trailing fractional zeroes, as seen in previous example, even though those are semantically insignificant for purposes such as equality checks.

Designing JSON documents

Representing data as JSON can be considerably more flexible than the traditional relational data model, which is compelling in environments where requirements are fluid. It is quite possible for both approaches to co-exist and complement each other within the same application. However, even for applications where maximal flexibility is desired, it is still recommended that JSON documents have a somewhat fixed structure. The structure is typically unenforced (though enforcing some business rules declaratively is possible), but having a predictable structure makes it easier to write queries that usefully summarize a set of JSON documents (datums) in a table.

JSON data is subject to the same concurrency-control considerations as any other data type when stored in a table. Although storing large documents is practicable, keep in mind that any update acquires a row-level lock on the whole row. Consider limiting JSON documents to a manageable size in order to decrease lock contention among updating transactions. Ideally, JSON documents should each represent an atomic datum that business rules dictate cannot reasonably be further subdivided into smaller datums that could be modified independently.

jsonb Containment and Existence

Testing containment is an important capability of jsonb. There is no parallel set of facilities for the json type. Containment tests whether one jsonb document has contained within it another one. These examples return true except as noted:

-- Simple scalar/primitive values contain only the identical value:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;
-- The array on the right side is contained within the one on the left:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
-- Order of array elements is not significant, so this is also true:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;
-- Duplicate array elements don't matter either:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;
-- The object with a single pair on the right side is contained
-- within the object on the left side:
SELECT '{"product": "Greenplum", "version": "6.0.0", "jsonb":true}'::jsonb @> '{"version":"6.0.0"}'::jsonb;
-- The array on the right side is not considered contained within the
-- array on the left, even though a similar array is nested within it:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- yields false
-- But with a layer of nesting, it is contained:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
-- Similarly, containment is not reported here:
SELECT '{"foo": {"bar": "baz", "zig": "zag"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- yields false
-- But with a layer of nesting, it is contained:
SELECT '{"foo": {"bar": "baz", "zig": "zag"}}'::jsonb @> '{"foo": {"bar": "baz"}}'::jsonb;

The general principle is that the contained object must match the containing object as to structure and data contents, possibly after discarding some non-matching array elements or object key/value pairs from the containing object. For containment, the order of array elements is not significant when doing a containment match, and duplicate array elements are effectively considered only once.

As an exception to the general principle that the structures must match, an array may contain a primitive value:

-- This array contains the primitive string value:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;
-- This exception is not reciprocal -- non-containment is reported here:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- yields false

jsonb also has an existence operator, which is a variation on the theme of containment: it tests whether a string (given as a text value) appears as an object key or array element at the top level of the jsonb value. These examples return true except as noted:

-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
-- String exists as object key:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';
-- Object values are not considered:
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- yields false
-- As with containment, existence must match at the top level:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false
-- A string is considered to exist if it matches a primitive JSON string:
SELECT '"foo"'::jsonb ? 'foo';

JSON objects are better suited than arrays for testing containment or existence when there are many keys or elements involved, because unlike arrays they are internally optimized for searching, and do not need to be searched linearly.

The various containment and existence operators, along with all other JSON operators and functions are documented in JSON Functions and Operators.

Because JSON containment is nested, an appropriate query can skip explicit selection of sub-objects. As an example, suppose that we have a doc column containing objects at the top level, with most objects containing tags fields that contain arrays of sub-objects. This query finds entries in which sub-objects containing both “term”:”paris” and “term”:”food” appear, while ignoring any such keys outside the tags array:

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

The query with this predicate could accomplish the same thing.

SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

However, the second approach is less flexible and is often less efficient as well.

On the other hand, the JSON existence operator is not nested: it will only look for the specified key or array element at top level of the JSON value.

jsonb Indexing

The Greenplum Database jsonb data type, supports GIN, btree, and hash indexes.

• GIN Indexes on jsonb Data
• Btree and Hash Indexes on jsonb Data

GIN Indexes on jsonb Data

GIN indexes can be used to efficiently search for keys or key/value pairs occurring within a large number of jsonb documents (datums). Two GIN operator classes are provided, offering different performance and flexibility trade-offs.

The default GIN operator class for jsonb supports queries with the @>, ?, ?& and ?| operators. (For details of the semantics that these operators implement, see the table Table 3.) An example of creating an index with this operator class is:

CREATE INDEX idxgin ON api USING gin (jdoc);

The non-default GIN operator class jsonb_path_ops supports indexing the @> operator only. An example of creating an index with this operator class is:

CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);

Consider the example of a table that stores JSON documents retrieved from a third-party web service, with a documented schema definition. This is a typical document:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

The JSON documents are stored a table named api, in a jsonb column named jdoc. If a GIN index is created on this column, queries like the following can make use of the index:

-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

However, the index could not be used for queries like the following. The operator ? is indexable, however, the comparison is not applied directly to the indexed column jdoc:

-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

With appropriate use of expression indexes, the above query can use an index. If querying for particular items within the tags key is common, defining an index like this might be worthwhile:

CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags'));

Now, the WHERE clause jdoc -> ‘tags’ ? ‘qui’ is recognized as an application of the indexable operator ? to the indexed expression jdoc -> ‘tags’. For information about expression indexes, see Indexes on Expressions.

Another approach to querying JSON documents is to exploit containment, for example:

-- Find documents in which the key "tags" contains array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

A simple GIN index on the jdoc column can support this query. However, the index will store copies of every key and value in the jdoc column, whereas the expression index of the previous example stores only data found under the tags key. While the simple-index approach is far more flexible (since it supports queries about any key), targeted expression indexes are likely to be smaller and faster to search than a simple index.

Although the jsonb_path_ops operator class supports only queries with the @> operator, it has performance advantages over the default operator class jsonb_ops. A jsonb_path_ops index is usually much smaller than a jsonb_ops index over the same data, and the specificity of searches is better, particularly when queries contain keys that appear frequently in the data. Therefore search operations typically perform better than with the default operator class.

The technical difference between a jsonb_ops and a jsonb_path_ops GIN index is that the former creates independent index items for each key and value in the data, while the latter creates index items only for each value in the data.

Note: For this discussion, the term value includes array elements, though JSON terminology sometimes considers array elements distinct from values within objects.

Basically, each jsonb_path_ops index item is a hash of the value and the key(s) leading to it; for example to index {“foo”: {“bar”: “baz”}}, a single index item would be created incorporating all three of foo, bar, and baz into the hash value. Thus a containment query looking for this structure would result in an extremely specific index search; but there is no way at all to find out whether foo appears as a key. On the other hand, a jsonb_ops index would create three index items representing foo, bar, and baz separately; then to do the containment query, it would look for rows containing all three of these items. While GIN indexes can perform such an AND search fairly efficiently, it will still be less specific and slower than the equivalent jsonb_path_ops search, especially if there are a very large number of rows containing any single one of the three index items.

A disadvantage of the jsonb_path_ops approach is that it produces no index entries for JSON structures not containing any values, such as {“a”: {}}. If a search for documents containing such a structure is requested, it will require a full-index scan, which is quite slow. jsonb_path_ops is ill-suited for applications that often perform such searches.

Btree and Hash Indexes on jsonb Data

jsonb also supports btree and hash indexes. These are usually useful only when it is important to check the equality of complete JSON documents.

For completeness the btree ordering for jsonb datums is:

Object > Array > Boolean > Number > String > Null
Object with n pairs > object with n - 1 pairs
Array with n elements > array with n - 1 elements

Objects with equal numbers of pairs are compared in the order:

key-1, value-1, key-2 ...

Object keys are compared in their storage order. In particular, since shorter keys are stored before longer keys, this can lead to orderings that might not be intuitive, such as:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

Similarly, arrays with equal numbers of elements are compared in the order:

element-1, element-2 ...

Primitive JSON values are compared using the same comparison rules as for the underlying Greenplum Database data type. Strings are compared using the default database collation.

JSON Functions and Operators

Greenplum Database includes built-in functions and operators that create and manipulate JSON data.

• JSON Operators
• JSON Creation Functions
• JSON Aggregate Functions
• JSON Processing Functions

Note: For json data type values, all key/value pairs are kept even if a JSON object contains duplicate keys. For duplicate keys, JSON processing functions consider the last value as the operative one. For the jsonb data type, duplicate object keys are not kept. If the input includes duplicate keys, only the last value is kept. See About JSON Data.

JSON Operators

This table describes the operators that are available for use with the json and jsonb data types.

Note: There are parallel variants of these operators for both the json and jsonb data types. The field, element, and path extraction operators return the same data type as their left-hand input (either json or jsonb), except for those specified as returning text, which coerce the value to text. The field, element, and path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example if no such element exists.

Operators that require the jsonb data type as the left operand are described in the following table. Many of these operators can be indexed by jsonb operator classes. For a full description of jsonb containment and existence semantics, see jsonb Containment and Existence. For information about how these operators can be used to effectively index jsonb data, see jsonb Indexing.

The standard comparison operators in the following table are available only for the jsonb data type, not for the json data type. They follow the ordering rules for B-tree operations described in jsonb Indexing.

Note: The != operator is converted to <> in the parser stage. It is not possible to implement != and <> operators that do different things.

JSON Creation Functions

This table describes the functions that create json data type values. (Currently, there are no equivalent functions for jsonb, but you can cast the result of one of these functions to jsonb.)

Note: array_to_json and row_to_json have the same behavior as to_json except for offering a pretty-printing option. The behavior described for to_json likewise applies to each individual value converted by the other JSON creation functions.

Note: The hstore module contains functions that cast from hstore to json, so that hstore values converted via the JSON creation functions will be represented as JSON objects, not as primitive string values.

JSON Aggregate Functions

This table shows the functions aggregate records to an array of JSON objects and pairs of values to a JSON object

JSON Processing Functions

This table shows the functions that are available for processing json and jsonb values.

Many of these processing functions and operators convert Unicode escapes in JSON strings to the appropriate single character. This is a not an issue if the input data type is jsonb, because the conversion was already done. However, for json data type input, this might result in an error being thrown. See About JSON Data.

 key | value
——-+———-
 a   | “foo”
 b   | “bar”

 key | value
——-+———-
 a   | foo
 b   | bar

 json_object_keys
—————————
 f1
 f2

 a | b
—-+—-
 1 | 2

 a | b
—-+—-
 1 | 2
 3 | 4

   value
—————-
 1
 true
 [2,false]

   value
—————-
 foo
 bar

 a |    b    | d
—-+————-+—-
 1 | [1,2,3] |

 a |  b
—-+——-
 1 | foo
 2 |

Note:

1. The examples for the functions json_populate_record(), json_populate_recordset(), json_to_record() and json_to_recordset() use constants. However, the typical use would be to reference a table in the FROM clause and use one of its json or jsonb columns as an argument to the function. The extracted key values can then be referenced in other parts of the query. For example the value can be referenced in WHERE clauses and target lists. Extracting multiple values in this way can improve performance over extracting them separately with per-key operators.

   JSON keys are matched to identical column names in the target row type. JSON type coercion for these functions might not result in desired values for some types. JSON fields that do not appear in the target row type will be omitted from the output, and target columns that do not match any JSON field will be NULL.

2. The json_typeof function null return value of null should not be confused with a SQL NULL. While calling json_typeof(‘null’::json) will return null, calling json_typeof(NULL::json) will return a SQL NULL.

Need help? Visit the Greenplum Database Community