JSON support

• YSQL
• YCQL

JSON data types are for storing JSON (JavaScript Object Notation) data, as specified in RFC 7159. Such data can also be stored as text, but the JSON data types have the advantage of enforcing that each stored value is valid according to the JSON rules. Assorted JSON-specific functions and operators are also available for data stored in these data types.

JSON functionality in YSQL is nearly identical to the JSON functionality in PostgreSQL.

YSQL supports the following two JSON data types:

• jsonb - does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

• json - stores an exact copy of the input text, and therefore preserves semantically-insignificant white space between tokens, as well as the order of keys in JSON objects. Also, if a JSON object in the value contains the same key more than once, all the key/value pairs are kept. The processing functions consider the last value as the operative one.

This section focuses on only the jsonb type.

Create a table

Create a basic table books with a primary key and one jsonb column doc that contains various details about each book.

yugabyte=# CREATE TABLE books(k int primary key, doc jsonb not null);

Next, insert some rows which contain details about various books. These details are represented as JSON documents, as shown below.

yugabyte=# INSERT INTO books(k, doc) values
  (1,
  '{ "ISBN"    : 4582546494267,
     "title"   : "Macbeth",
     "author"  : {"given_name": "William", "family_name": "Shakespeare"},
     "year"    : 1623}'),

  (2,
  '{ "ISBN"    : 8760835734528,
     "title"   : "Hamlet",
     "author"  : {"given_name": "William", "family_name": "Shakespeare"},
     "year"    : 1603,
     "editors" : ["Lysa", "Elizabeth"] }'),

  (3,
  '{ "ISBN"    : 7658956876542,
     "title"   : "Oliver Twist",
     "author"  : {"given_name": "Charles", "family_name": "Dickens"},
     "year"    : 1838,
     "genre"   : "novel",
     "editors" : ["Mark", "Tony", "Britney"] }'),
  (4,
  '{ "ISBN"    : 9874563896457,
     "title"   : "Great Expectations",
     "author"  : {"family_name": "Dickens"},
     "year"    : 1950,
     "genre"   : "novel",
     "editors" : ["Robert", "John", "Melisa", "Elizabeth"] }'),

  (5,
  '{ "ISBN"    : 8647295405123,
     "title"   : "A Brief History of Time",
     "author"  : {"given_name": "Stephen", "family_name": "Hawking"},
     "year"    : 1988,
     "genre"   : "science",
     "editors" : ["Melisa", "Mark", "John", "Fred", "Jane"] }'),

  (6,
  '{
    "ISBN"     : 6563973589123,
    "year"     : 1989,
    "genre"    : "novel",
    "title"    : "Joy Luck Club",
    "author"   : {"given_name": "Amy", "family_name": "Tan"},
    "editors"  : ["Ruilin", "Aiping"]}');

Query JSON documents

List all the rows thus:

yugabyte=# SELECT * FROM books;

This is the result:

 k |                                                                                                         doc
---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 5 | {"ISBN": 8647295405123, "year": 1988, "genre": "science", "title": "A Brief History of Time", "author": {"given_name": "Stephen", "family_name": "Hawking"}, "editors": ["Melisa", "Mark", "John", "Fred", "Jane"]}
 1 | {"ISBN": 4582546494267, "year": 1623, "title": "Macbeth", "author": {"given_name": "William", "family_name": "Shakespeare"}}
 6 | {"ISBN": 6563973589123, "year": 1989, "genre": "novel", "title": "Joy Luck Club", "author": {"given_name": "Amy", "family_name": "Tan"}, "editors": ["Ruilin", "Aiping"]}
 4 | {"ISBN": 9874563896457, "year": 1950, "genre": "novel", "title": "Great Expectations", "author": {"family_name": "Dickens"}, "editors": ["Robert", "John", "Melisa", "Elizabeth"]}
 2 | {"ISBN": 8760835734528, "year": 1603, "title": "Hamlet", "author": {"given_name": "William", "family_name": "Shakespeare"}, "editors": ["Lysa", "Elizabeth"]}
 3 | {"ISBN": 7658956876542, "year": 1838, "genre": "novel", "title": "Oliver Twist", "author": {"given_name": "Charles", "family_name": "Dickens"}, "editors": ["Mark", "Tony", "Britney"]}
(6 rows)

Using -> and ->>

YSQL has two native operators, the -> operator and the ->> operator, to query JSON documents. The -> operator returns a JSON object, while the ->> operator returns text. These operators work on both JSON as well as JSONB columns to select a subset of attributes as well as to inspect the JSON document.

The following example shows how to select a few attributes from each document.

yugabyte=# SELECT doc->'title' AS book_title,
              CONCAT(doc->'author'->'family_name',
              ', ', doc->'author'->'given_name') AS author
            FROM books;

This is the result:

        book_title         |          author
---------------------------+--------------------------
 "A Brief History of Time" | "Hawking", "Stephen"
 "Macbeth"                 | "Shakespeare", "William"
 "Joy Luck Club"           | "Tan", "Amy"
 "Great Expectations"      | "Dickens",
 "Hamlet"                  | "Shakespeare", "William"
 "Oliver Twist"            | "Dickens", "Charles"
(6 rows)

Because the -> operator returns an object, you can chain it to inspect deep into a JSON document, as follows:

yugabyte=# SELECT '{"title": "Macbeth", "author": {"given_name": "William"}}'::jsonb
              -> 'author' -> 'given_name' as first_name;

This is the result:

 first_name
------------
 "William"
(1 row)

Existence with ?

The ? operator can be used to check if a JSON document contains a certain attribute. For example, if you want to find a count of the records where the doc column contains a property named genre, run the following statement:

yugabyte=# SELECT doc->'title' AS book_title,
              doc->'genre' AS genre
              FROM books WHERE doc ? 'genre';

This is the result:

        book_title         |   genre
---------------------------+-----------
 "A Brief History of Time" | "science"
 "Joy Luck Club"           | "novel"
 "Great Expectations"      | "novel"
 "Oliver Twist"            | "novel"
(4 rows)

Containment with @>

The containment operator @> tests whether one document contains another. If you want to find all books that contain the JSON value {"author": {"given_name": "William"}} (in other words, the author of the book has the given name William), do the following:

yugabyte=# SELECT doc->'title' AS book_title,
              CONCAT(doc->'author'->'family_name',
              ', ', doc->'author'->'given_name') AS author
            FROM books
            WHERE doc @> '{"author": {"given_name": "William"}}'::jsonb;

This is the result:

 book_title |          author
------------+--------------------------
 "Macbeth"  | "Shakespeare", "William"
 "Hamlet"   | "Shakespeare", "William"
(2 rows)

Update JSON documents

You can update a JSON document in a number of ways, as shown in the following examples.

Add an attribute

Use the || operator to either update or insert the attribute into the existing JSON document. For example, if you want to add a stock attribute to all the books, do the following:

yugabyte=# UPDATE books SET doc = doc || '{"stock": "true"}';

This is the result:

yugabyte=# SELECT doc->'title' AS title, doc->'stock' AS stock FROM books;

           title           | stock
---------------------------+--------
 "A Brief History of Time" | "true"
 "Macbeth"                 | "true"
 "Joy Luck Club"           | "true"
 "Great Expectations"      | "true"
 "Hamlet"                  | "true"
 "Oliver Twist"            | "true"
(6 rows)

Remove an attribute

Use the - operator to remove an attribute:

yugabyte=# UPDATE books SET doc = doc - 'stock';

This removes the field from all the documents, as shown below.

yugabyte=# SELECT doc->'title' AS title, doc->'stock' AS stock FROM books;

           title           | stock
---------------------------+-------
 "A Brief History of Time" |
 "Macbeth"                 |
 "Joy Luck Club"           |
 "Great Expectations"      |
 "Hamlet"                  |
 "Oliver Twist"            |
(6 rows)

Replace a document

To replace an entire document, run the following SQL statement:

UPDATE books
    SET doc = '{"ISBN": 4582546494267, "year": 1623, "title": "Macbeth", "author": {"given_name": "William", "family_name": "Shakespeare"}}'
    WHERE k=1;

Built-in functions

YSQL supports a large number of operators and built-in functions that operate on JSON documents. This section highlights a few of these built-in functions.

YSQL supports all of the built-in functions supported by PostgreSQL. For a complete list, refer to JSON functions and operators.

Expand JSON - jsonb_each

The jsonb_each() function expands the top-level JSON document into a set of key-value pairs, as shown below.

yugabyte=# SELECT jsonb_each(doc) FROM books WHERE k=1;

The output is shown below.

                                 jsonb_each
----------------------------------------------------------------------------
 (ISBN,4582546494267)
 (year,1623)
 (title,"""Macbeth""")
 (author,"{""given_name"": ""William"", ""family_name"": ""Shakespeare""}")
(4 rows)

Retrieve keys - jsonb_object_keys

The jsonb_object_keys() function retrieves the keys of the top-level JSON document thus:

yugabyte=# SELECT jsonb_object_keys(doc) FROM books WHERE k=1;

This is the result:

 jsonb_object_keys
-------------------
 ISBN
 year
 title
 author
(4 rows)

Format JSON - jsonb_pretty

When you select a jsonb (or json) value in ysqlsh, you see the terse text typecast of the value. The jsonb_pretty() function returns a more human-readable format:

yugabyte=# SELECT jsonb_pretty(doc) FROM books WHERE k=1;

This is the result:

             jsonb_pretty
--------------------------------------
 {                                   +
     "ISBN": 4582546494267,          +
     "year": 1623,                   +
     "title": "Macbeth",             +
     "author": {                     +
         "given_name": "William",    +
         "family_name": "Shakespeare"+
     }                               +
 }
(1 row)

Constraints

You can create constraints on jsonb data types. This section includes some examples. For a fuller discussion, refer to Create indexes and check constraints on JSON columns.

Check JSON documents are objects

Here's how to insist that each JSON document is an object:

alter table books
add constraint books_doc_is_object
check (jsonb_typeof(doc) = 'object');

Check ISBN is a 13-digit number

Here's how to insist that the ISBN is always defined and is a positive 13-digit number:

alter table books
add constraint books_isbn_is_positive_13_digit_number
check (
  (doc->'ISBN') is not null
    and
  jsonb_typeof(doc->'ISBN') = 'number'
     and
  (doc->>'ISBN')::bigint > 0
    and
  length(((doc->>'ISBN')::bigint)::text) = 13
);

Indexes on JSON attributes

Indexes are essential to perform efficient lookups by document attributes. Without indexes, queries on document attributes end up performing a full table scan and process each JSON document. This section outlines some of the indexes supported.

Secondary index

If you want to support range queries that reference the value for the year attribute, do the following:

CREATE INDEX books_year
    ON books (((doc->>'year')::int) ASC)
    WHERE doc->>'year' is not null;

This will make the following query efficient:

select
  (doc->>'ISBN')::bigint as isbn,
  doc->>'title'          as title,
  (doc->>'year')::int    as year
from books
where (doc->>'year')::int > 1850
and doc->>'year' IS NOT NULL
order by 3;

Partial and expression indexes

You might want to index only those documents that contain the attribute (as opposed to indexing the rows that have a NULL value for that attribute). This is a common scenario because not all the documents would have all the attributes defined. This can be achieved using a partial index.

In the previous section where you created a secondary index, not all the books may have the year attribute defined. Suppose that you want to index only those documents that have a NOT NULL year attribute. Create the following partial index:

CREATE INDEX books_year
    ON books ((doc->>'year') ASC)
    WHERE doc->>'year' IS NOT NULL;

Unique index

You can create a unique index on the "ISBN" key for the books table as follows:

CREATE UNIQUE INDEX books_isbn_unq on books((doc->>'ISBN'));

Inserting a row with a duplicate value would fail as shown below. The book has a new primary key k but an existing ISBN, 4582546494267.

yugabyte=# INSERT INTO books values
           (7, '{  "ISBN"    : 4582546494267,
                   "title"   : "Fake Book with duplicate ISBN" }');

ERROR:  23505: duplicate key value violates unique constraint "books_isbn_unq"

Read more

• JSON data types and functionality reference
• JSON functions and operators
• Create indexes and check constraints on JSON columns