Using JSON

Comparing JSON and LONGTEXT Columns

A JSON column is analogous to a LONGTEXT column in the following ways:

• JSON columns can store arbitrarily large JSON values in a normalized text representation.

• JSON columns have the same storage requirement, as if the JSON value were stored in a text column.

The key difference is that JSON data is stored in a normalized format, which makes many operations faster than if the data were stored manually in a text column. The following is an example of non-normalized data, which is valid JSON but is relatively difficult to parse:

'{ "b\u000a": 1,"a": 2 ,"a":3 } '

Normalized data, on the other hand, is easier to parse because duplicate keys are merged, keys are sorted by value, and extraneous whitespace is removed.

'{"a":3,"b\n":1}'

Defining JSON Columns

Defining a JSON column in a SingleStore table is as simple as specifying the JSON data type in the CREATE TABLE command:

CREATE TABLE assets (
   tag_id BIGINT PRIMARY KEY,
   name TEXT NOT NULL,
   description TEXT,
   properties JSON NOT NULL);

JSON Collation

The collation for a JSON column can be set explicitly using a COLLATE clause. When the collation is not set explicitly, the collation is controlled by the json_collation engine variable.

Refer to Sync Variables Lists, Character Set and Collation Override, Unicode Support, and Character Encodings and Collation for more information.

Indexing Data in JSON Columns

JSON columns are not indexed directly - they are indexed using computed columns. For the fastest performance, you should not use JSON built-ins or :: notation in your filters. Instead, create a computed column that includes the JSON column in the computation, and then use the computed column for the index. In this way, the index gets updated only when the relevant JSON data is updated in a row.

CREATE TABLE assets (
   tag_id BIGINT PRIMARY KEY,
   name TEXT NOT NULL,
   description TEXT,
   properties JSON NOT NULL,
   weight AS properties::%weight PERSISTED DOUBLE,
   license_plate AS properties::$license_plate PERSISTED LONGTEXT,
   KEY(license_plate), KEY(weight));

JSON computed columns that are indexed will be utilized by the optimizer more efficiently. Queries that use indexed computed columns as filters or sorts, will perform faster by avoiding expression evaluation, and by seeking into or searching the indexes rather than scanning tables. In the following examples, we will use the :: notation in the filtering and sorting clauses to illustrate how the optimizer matches the computed columns.

EXPLAIN SELECT * FROM assets WHERE properties::$license_plate = "VGB116";

+------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                          |
+------------------------------------------------------------------------------------------------------------------+
| Gather partitions:all alias:remote_0                                                                             |
| Project [assets.tag_id, assets.name, assets.description, assets.properties, assets.weight, assets.license_plate] |
| ColumnStoreFilter [assets.license_plate = 'VGB116' index]                                                       |
| ColumnStoreScan test1.assets, KEY __UNORDERED () USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore      |
+------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

EXPLAIN SELECT * FROM assets ORDER BY properties::%weight;

+------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                          |
+------------------------------------------------------------------------------------------------------------------+
| GatherMerge [remote_0.weight] partitions:all alias:remote_0                                                      |
| Project [assets.tag_id, assets.name, assets.description, assets.properties, assets.weight, assets.license_plate] |
| Sort [assets.weight]                                                                                             |
| ColumnStoreScan test1.assets, KEY __UNORDERED () USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore      |
+------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Inserting Data into a JSON Column

When inserting a row in a table, specifying JSON data is straightforward. For example: Given a table t defined as CREATE TABLE test_table(col_a TEXT, col_b JSON);, you can insert a row into test_table as follows:

INSERT INTO test_table(col_a,col_b) VALUES ('hello','{"x":"goodbye","y":"goodnight"}');

Columnstore Tables Having JSON Columns with Null Values or Empty Arrays

By default, SingleStore preserves columnstore JSON NULL values and empty arrays. To change this behavior and disable this setting, set the preserve_original_colstore_json global variable to OFF. This flag preserves the original columnstore JSON for any new data that is loaded; it does not update the existing data. By default, this variable is set to AUTO (same as ON).

CREATE TABLE json_empty_values_table(a INT, b JSON, SORT KEY (a));

INSERT INTO json_empty_values_table VALUES (1, '{"v":null}');
INSERT INTO json_empty_values_table VALUES (2, '{"w":[]}');
INSERT INTO json_empty_values_table VALUES (3, '{"x":"foo","y":null,"z":[]}');
INSERT INTO json_empty_values_table VALUES (4, 'null');
INSERT INTO json_empty_values_table VALUES (5, '[]');

OPTIMIZE TABLE json_empty_values_table FLUSH;

SELECT * FROM json_empty_values_table ORDER BY a;

+------+-----------------------------+
| a    | b                           |
+------+-----------------------------+
|    1 | {"v":null}                  |
|    2 | {"w":[]}                    |
|    3 | {"x":"foo","y":null,"z":[]} |
|    4 | null                        |
|    5 | []                          |
+------+-----------------------------+

Accessing Fields in a JSON Object

To access a field of a JSON object stored in a column, use the name of the column suffixed with ::keyName. For example, if column data contains {"bits":[true,false]}, then the expression data::bits evaluates to the JSON value [true,false].

INSERT INTO TestJSON VALUES ('{"first":"hello"}');

SELECT data::first, data::$first FROM TestJSON;

+-------------+--------------+
| data::first | data::$first |
+-------------+--------------+
| "hello"     | hello        |
+-------------+--------------+

INSERT INTO TestJSON VALUES ('{"first":"hello", "second":"world"}');

SELECT CONCAT(data::$first, ' ', data::$second) FROM TestJSON;

+------------------------------------------+
| CONCAT(data::$first, ' ', data::$second) |
+------------------------------------------+
| hello world                              |
+------------------------------------------+

SELECT json, json::a::`2` FROM (SELECT '{"a":[1,2,3,4]}' AS json) sub;

+-----------------+--------------+
| json            | json::a::`2` |
+-----------------+--------------+
| {"a":[1,2,3,4]} | 3            |
+-----------------+--------------+

SELECT json, json_extract_json(json, 'a', 1+1) FROM (SELECT '{"a":[1,2,3,4]}' AS json) sub;

+-----------------+-----------------------------------+
| json            | json_extract_json(json, 'a', 1+1) |
+-----------------+-----------------------------------+
| {"a":[1,2,3,4]} | 3                                 |
+-----------------+-----------------------------------+

CREATE TABLE json_tab (`id` INT(11) DEFAULT NULL,`jsondata` JSON COLLATE utf8_bin);

INSERT INTO json_tab VALUES 
( 8765 ,' {"city":"SFO","sports_teams":[{"sport_name":"football","teams":  [{"club_name":"Raiders"},{"club_name":"49ers"}]},
{"sport_name":"baseball","teams" : [{"club_name":"As"},{"club_name":"SF Giants"}]}]}') ; 

INSERT INTO json_tab VALUES 
( 9876,'{"city":"NY","sports_teams" : [{ "sport_name":"football","teams" : [{ "club_name":"Jets"},{"club_name":"Giants"}]},
{"sport_name":"baseball","teams" : [ {"club_name":"Mets"},{"club_name":"Yankees"}]},
{"sport_name":"basketball","teams" : [{"club_name":"Nets"},{"club_name":"Knicks"}]}]}');

WITH t AS(
SELECT id, jsondata::city city , table_col AS sports_clubs FROM json_tab JOIN TABLE(JSON_TO_ARRAY(jsondata::sports_teams))),

t1 AS(
SELECT t.id, t.city, t.sports_clubs::sport_name sport, table_col AS clubs FROM t JOIN TABLE(JSON_TO_ARRAY(t.sports_clubs::teams)))

SELECT t1.id, t1.city,t1.sport,t1.clubs::club_name club_name FROM t1;

+------+-------+--------------+-------------+
| id   | city  | sport        | club_name   |
+------+-------+--------------+-------------+
| 9876 | "NY"  | "football"   | "Jets"      |
| 9876 | "NY"  | "football"   | "Giants"    |
| 9876 | "NY"  | "baseball"   | "Mets"      |
| 9876 | "NY"  | "baseball"   | "Yankees"   |
| 9876 | "NY"  | "basketball" | "Nets"      |
| 9876 | "NY"  | "basketball" | "Knicks"    |
| 8765 | "SFO" | "football"   | "Raiders"   |
| 8765 | "SFO" | "football"   | "49ers"     |
| 8765 | "SFO" | "baseball"   | "As"        |
| 8765 | "SFO" | "baseball"   | "SF Giants" |
+------+-------+--------------+-------------+

WITH t AS
(SELECT id, jsondata::city city , table_col AS sports_clubs FROM json_tab JOIN TABLE(JSON_TO_ARRAY(jsondata::sports_teams))),

t1 AS
(SELECT t.id, t.city, t.sports_clubs::sport_name sport, table_col AS clubs FROM t JOIN TABLE(JSON_TO_ARRAY(t.sports_clubs::teams))) 

SELECT t1.id, t1.city,t1.sport,t1.clubs::club_name club_name FROM t1 WHERE t1.clubs::$club_name = 'Yankees';

+------+------+------------+-----------+
| id   | city | sport      | club_name |
+------+------+------------+-----------+
| 9876 | "NY" | "baseball" | "Yankees" |
+------+------+------------+-----------+

CREATE TABLE test_json(col_a json);

INSERT INTO test_json VALUES ('{"addParams": "{\\"Emp_Id\\":\\"1487\\", 
  \\"Emp_LastName\\":\\"Stephens\\",\\"Emp_FirstName\\":\\"Mark\\",\\"Dept\\":\\"Support\\"}"}');

SELECT * FROM test_json;

+------------------------------------------------------------------------------------+
| col_a                                                                              |
+------------------------------------------------------------------------------------+
| {"addParams":"{\"Emp_Id\":\"1487\",                                                |
|   \"Emp_LastName\":\"Stephens\",\"Emp_FirstName\":\"Mark\",\"Dept\":\"Support\"}"} |
+------------------------------------------------------------------------------------+  

UPDATE users SET userdata::name::$first = 'Alex';
UPDATE users SET userdata = JSON_SET_STRING(userdata, 'name', 'first', 'Alex');

SELECT ticker_symbol FROM stocks WHERE statistics::%`P/E` > 1.5;
SELECT ticker_symbol FROM stocks WHERE JSON_EXTRACT_DOUBLE(statistics, 'P/E') > 1.5;

JSON Array Performance

Aggregations over array objects using the JOIN TABLE(JSON_TO_ARRAY()) operation are optimized in SingleStore to run efficiently on large batches of rows in parallel. The optimization also reduces the amount of data read from the JSON column by retrieving only the necessary data instead of fetching the entire JSON object.

The following example uses the TPC-H data. Load this TPC-H data into your cluster, and run the following queries to modify the data for the current use case:

ALTER TABLE orders ADD COLUMN lineitems_json JSON;

CREATE TABLE new_table (
 l_orderkey bigint(11) NOT NULL,
 lineitems JSON
);

INSERT INTO new_table
SELECT l_orderkey,
      JSON_AGG(
          JSON_BUILD_OBJECT(
              'l_partkey', l_partkey,
              'l_suppkey', l_suppkey,
              'l_linenumber', l_linenumber,
              'l_quantity', l_quantity,
              'l_extendedprice', l_extendedprice,
              'l_discount', l_discount,
              'l_tax', l_tax,
              'l_returnflag', l_returnflag,
              'l_linestatus', l_linestatus,
              'l_shipdate', l_shipdate,
              'l_commitdate', l_commitdate,
              'l_receiptdate', l_receiptdate,
              'l_shipinstruct', l_shipinstruct,
              'l_shipmode', l_shipmode,
              'l_comment', l_comment
          )
      ) as lineitems
FROM lineitem
GROUP BY l_orderkey;

UPDATE orders o
JOIN new_table t ON o.o_orderkey = t.l_orderkey
SET o.lineitems_json = t.lineitems;

Here are a few sample query structures that benefit from the optimization.

• The following query calculates the total number of line items for each shipping mode by aggregating and grouping on the JSON array columns:

  SQL

  Copy

  SELECT t.table_col::$l_shipmode, sum(t.table_col::%l_quantity) as quantity
  FROM orders
  JOIN TABLE(JSON_TO_ARRAY(lineitems_json)) t
  GROUP BY t.table_col::$l_shipmode;

  +--------------------------+-----------+
  | t.table_col::$l_shipmode | quantity  |
  +--------------------------+-----------+
  | MAIL                     | 218541962 |
  | FOB                      | 218469194 |
  | RAIL                     | 218553230 |
  | AIR                      | 218525124 |
  | REG AIR                  | 218617001 |
  | SHIP                     | 218576988 |
  | TRUCK                    | 218454537 |
  +--------------------------+-----------+

• The following query calculates the total number of line items for each order priority:

  SQL

  Copy

  SELECT o_orderpriority as priority, sum(t.table_col::$l_quantity) as quantity
  FROM orders JOIN TABLE(JSON_TO_ARRAY(lineitems_json)) t
  GROUP BY o_orderpriority;

  +-----------------+-----------+
  | priority        | quantity  |
  +-----------------+-----------+
  | 3-MEDIUM        | 305731841 |
  | 5-LOW           | 305656541 |
  | 2-HIGH          | 306112515 |
  | 1-URGENT        | 306300507 |
  | 4-NOT SPECIFIED | 305936632 |
  +-----------------+-----------+

• The following query performs an intricate aggregation operation over multiple fields:

  SQL

  Copy

  SELECT
     t.table_col::$l_returnflag as r,
     t.table_col::$l_linestatus as s,
     sum(t.table_col::%l_quantity) as sum_qty,
     sum(t.table_col::%l_extendedprice) as sum_base_price,
     sum(t.table_col::%l_extendedprice * (1 - t.table_col::%l_discount)) as sum_disc_price,
     avg(t.table_col::%l_quantity) as avg_qty
  FROM orders
  JOIN TABLE(JSON_TO_ARRAY(lineitems_json)) t
  GROUP by r, s

  +---+---+-----------+--------------------+--------------------+--------------------+
  | r | s | sum_qty   | sum_base_price     | sum_disc_price     | avg_qty            |
  +---+---+-----------+--------------------+--------------------+--------------------+
  | A | F | 377518399 |  566065727797.2633 | 537759104278.07465 | 25.500975103007097 |
  | R | F | 377732830 |  566431054976.0034 |  538110922664.7574 |  25.50838478968014 |
  | N | F |   9851614 | 14767438399.169971 | 14028805792.211407 | 25.522448302840946 |
  | N | O | 764635193 | 1146548935600.9531 | 1089215873201.9531 | 25.498214140059787 |
  +---+---+-----------+--------------------+--------------------+--------------------+

Infinities and NaNs

SingleStore does not attempt to support entities such as the nan (not a number) entity, as in {"value":nan}. Although the JavaScript language supports nan and positive and negative infinities, neither the JSON standard nor SQL’s DOUBLE type provide any way to represent such non-finite values. If your application requires these special values, you might try using null, 0.0, or strings (such as "nan" and "inf") in their place.

Unicode Support

JSON strings can contain any of the 65536 Unicode characters in the Basic Multilingual Plane, either directly as themselves or escaped with JSON’s \uABCD notation. JSON normalization converts all such ASCII escape sequences into UTF-8 byte sequences, except for those characters that must remain \u-encoded to keep the string valid JSON.

In SingleStore, a backslash (\) literal in a string must be escaped with a backslash. Therefore, pattern strings containing backslashes will have two backslash characters (\\).

Here is an example of how to use escaped notation for characters in the Basic Multilingual Plane:

SELECT '{"a":"\\u00F9"}' :> JSON;

+---------------------------+
| '{"a":"\\u00F9"}' :> JSON |
+---------------------------+
| {"a":"ù"}                 |
+---------------------------+

Like MySQL, SingleStore supports characters outside the Basic Multilingual Plane (characters whose codepoints are in the range 0x10000 to 0x10FFFF). This includes some uncommon Chinese characters and symbols such as emojis.

The json_collation engine variable must be set to utf8mb4_bin for these characters to work. Refer to Sync Variables Lists for more information.

Character Encodings and Collation

Collation provides a set of rules to use when comparing characters in a character set. By default, JSON columns use the collation specified in the json_collation engine variable (Sync Variables Lists). The collation of a JSON column affects the following:

• Output of SELECT DISTINCT, ORDER BY, and other queries that compare entire values.

• Identification of duplicate keys inside a single JSON object during normalization. For example, whether the string {"Schlüssel":1,"Schluessel":2} is normalized to {"Schluessel":2}.

• Sorting of keys inside a single JSON object. For example, whether the string {"Schlüssel":1,"Schluff":2} is normalized to {"Schluff":2,"Schlüssel":1}.

The default collation generally provides the desired behavior. However, you can override the default at the table or column level or by using the json_collation engine variable.

CREATE TABLE users (
   uid INT AUTO_INCREMENT PRIMARY KEY,
   userdata JSON,
   lastname AS data::name::$last PERSISTED TEXT) COLLATE=utf8_unicode_ci;

CREATE TABLE orders (
    oid INT AUTO_INCREMENT PRIMARY KEY,
    data JSON COLLATE utf8_unicode_ci,
    productname AS data::product::$name PERSISTED TEXT,
    productdetails AS data::product::$details PERSISTED TEXT COLLATE utf8_bin,
    comments VARCHAR(256));

:> text collate <colation_name>

SELECT *
FROM sets
WHERE sets.json_field::$x :> text collate utf8_bin = "string1"
AND sets.json_field::$y :> text collate utf8_general_ci = "string2";

Maximum JSON Value Size

Under the hood, JSON data is stored in LONGBLOB columns. While the DDL will allow you to specify a length of up to 4GB, there is an internal limit applied when assigning a value to a string or JSON field that caps the maximum size of a single value to max_allowed_packet. This is 100MB by default and can be set to up to 1GB.

Encoding Nested Columns

Parquet uses the Dremel encoding for nested columns with definition and repetition levels.

• Definition levels specify how many optional fields in the path for the column are defined.

• Repetition levels specify the repeated value of the field in the path.

• SingleStore stores the leaf columns for the JSON schema along with their respective repetition and definition levels.

Each of these internal columns will correspond to the value, definition level and repetition level columns which are encoded in SingleStore encodings (SeekableString, etc).

The example tables will have these values for the internal columns: