JSON

Background information

In the era of big data, a variety of solutions are available to retrieve structured data. However, most big data is semi-structured, and the amount of semi-structured data is rapidly increasing. Semi-structured data is much more difficult to understand and analyze than structured data. Mature solutions are required to process semi-structured data. AnalyticDB for MySQL provides JSON indexes as a semi-structured data retrieval feature to simplify semi-structured data processing.

Usage notes

• If you set a specific column to a JSON data type during table creation, AnalyticDB for MySQL will automatically create a JSON index for the column, and this JSON index cannot be modified.
• JSON supports the following data types: BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT, and NULL. A value of the NUMBER type must be within the allowed value range of the DOUBLE type. Otherwise, an error is returned.
• AnalyticDB for MySQL supports the JSON format. You must conform to the JSON syntax when you enter JSON strings.
• You can set only the default value of newly created columns to NULL.

Create a table

In the following example, the vj field is of the JSON type. After the table is created, AnalyticDB for MySQL automatically creates a JSON index for the vj column.

CREATE TABLE json_test(
  id int,
  vj json COMMENT 'The vj field is of the JSON type. AnalyticDB for MySQL automatically creates an index for this column.'
)
DISTRIBUTED BY HASH(id);

Limits

When you write JSON data into AnalyticDB for MySQL, take note of the following limits on keys and values:

• key: You must enclose a key with double quotation marks ("").
• value:
  • If a value is of the STRING type, you must enclose the value with double quotation marks ("").

    If a value is of the STRING type and contains double quotation marks (""), you must add the escape character before the double quotation marks to indicate that it is part of the value. For example, if a value is {"addr":"xyz"ab"c"}, it must be escaped to {"addr":"xyz\"ab\"c"}.

  • If a value is of the NUMBER type, you do not need to enclose the value with double quotation marks (""). You can directly write the value to AnalyticDB for MySQL.
  • If a value is of the BOOLEAN type, you must write TRUE or FALSE instead of 1 or 0 to AnalyticDB for MySQL.
  • If a value is NULL, you must write NULL to AnalyticDB for MySQL.
• AnalyticDB for MySQL allows you to write JSON arrays, including plain arrays and nested arrays.

  Examples: {"hobby":["basketball", "football"]} and {"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]}.

Write data into a table

Fields of the JSON type are written to tables in single quotation marks ('), in the same manner as fields of the VARCHAR type.

The following SQL statements that contain multiple examples of JSON data types are provided for your reference:

insert into json_test values(0, '{"id":0, "name":"abc", "age":0}');
insert into json_test values(1, '{"id":1, "name":"abc", "age":10, "gender":"femal"}');
insert into json_test values(2, '{"id":3, "name":"xyz", "age":30, "company":{"name":"alibaba", "place":"hangzhou"}}');
insert into json_test values(3, '{"id":5, "name":"abc", "age":50, "company":{"name":"alibaba", "place":"america"}}');
insert into json_test values (4, '{"a":1, "b":"abc-char", "c":true}');
insert into json_test values (5, '{"uname":{"first":"lily", "last":"chen"}, "addr":[{"city":"beijing", "no":1}, {"city":"shenzhen", "no":0}], "age":10, "male":true, "like":"fish", "hobby":["basketball", "football"]}');

Query data

AnalyticDB for MySQL allows you to use the json_extract function to query data.

• Syntax

  json_extract(json, jsonpath)

• Description

  This function returns the value specified by jsonpath from json.

• Parameters
  • json: the name of the column of the JSON type.
  • jsonpath: the path of the key, which is separated by periods (.). $ indicates the outermost path.

  For more information about JSON functions, see JSON functions.

• Example
  • Basic query

    select json_extract(vj,'$.name') from json_test where id=1;

  • Equivalent query

    select id, vj from json_test where json_extract(vj, '$.name') = 'abc';
    select id, vj from json_test where json_extract(vj, '$.c') = true;
    select id, vj from json_test where json_extract(vj, '$.age') = 30;
    select id, vj from json_test where json_extract(vj, '$.company.name') = 'alibaba';

  • Range query

    select id, vj from json_test where json_extract(vj, '$.age') > 0;
    select id, vj from json_test where json_extract(vj, '$.age') < 100;
    select id, vj from json_test where json_extract(vj, '$.name') > 'a' and json_extract(vj, '$.name') < 'z';

  • IS NULL or IS NOT NULL query

    select id, vj from json_test where json_extract(vj, '$.remark') is null;
    select id, vj from json_test where json_extract(vj, '$.name') is null;
    select id, vj from json_test where json_extract(vj, '$.name') is not null;
                                

  • IN query

    select id, vj from json_test where json_extract(vj, '$.name') in ('abc','xyz');
    select id, vj from json_test where json_extract(vj, '$.age') in (10,20);

  • LIKE query

    select id, vj from json_test where json_extract(vj, '$.name') like 'ab%';
    select id, vj from json_test where json_extract(vj, '$.name') like '%bc%';
    select id, vj from json_test where json_extract(vj, '$.name') like '%bc';

  • ARRAY query

    select id, vj from json_test where json_extract(vj, '$.addr[0].city') = 'beijing' and json_extract(vj, '$.addr[1].no') = 0;
    select id, vj from json_test where json_extract(vj, '$.addr[1].city') = 'shenzhen' and json_extract(json_test, '$.addr.no') = 0;

    Note You can use subscripts when you query arrays. However, you cannot iterate through an entire array.