JSON type

  • JSON

Description

The JSON type is used to store JSON data according to the standard syntax described by in the specification.

For storing such data it is possible to use VARCHAR type, but when using JSON type the data will be checked for conformity of input values to JSON format, so it is more convenient to use special JSON type in such cases. Besides, special functions are available for the JSON type, which allow you to directly access data in the JSON structure.

Inverted commas inside the text in JSON format should be double. And the quotes framing this text inside the INSERT expression must be single (see the example below).

Examples

Let’s create a js_table table and insert the data in JSON format from this example into the js_data column:

CREATE TABLE js_table(name VARCHAR, js_data JSON);

INSERT INTO js_table VALUES
('John Smith',
'{
  "first_name": "John",
  "last_name": "Smith",
  "is_alive": true,
  "age": 27,
  "address": {
    "street_address": "21 2nd Street",
    "city": "New York",
    "state": "NY",
    "postal_code": "10021-3100"
  },
  "phone_numbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    }
  ],
  "children": [
    "Catherine",
    "Thomas",
    "Trevor"
  ],
  "spouse": null
}');

Derive the top-level field names from the loaded JSON data using the json_keys function:

SELECT
    json_keys(js_data) AS json_fields
FROM js_table;
+---------------------------------------------------------------------------+
|                                json_fields                                |
+---------------------------------------------------------------------------+
| {first_name,last_name,is_alive,age,address,phone_numbers,children,spouse} |
+---------------------------------------------------------------------------+

Let’s extract the full name from the name text column into separate columns, and take the age age and the number of children from the JSON data — the length of the array in the children field. To do this, we use the json_extract and json_array_length functions.

SELECT
    name,
    json_extract(js_data, 'age') AS age,
    json_array_length(js_data, 'children') AS children_num
FROM js_table;
+------------+-----+--------------+
|    name    | age | children_num |
+------------+-----+--------------+
| John Smith | 27  | 3            |
+------------+-----+--------------+

See also