Questions tagged [mysql-json]

DO NOT USE FOR BASIC JSON. Use for questions about the JSON data type in MySQL 5.7 or later. Use with the [mysql] tag.

Overview

MySQL 5.7.8 introduced a new native json data type.

This data type allows the developer to store and manipulate JSON-encoded data more efficiently, in comparison to JSON data stored as the more common string data type.

Insert

Data inserted must be valid JSON syntax

INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

Select

Retrieving data directly from a JSON column

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

Create table example

The following is a data-definition statement that defines an auto-generated JSON Object that reflects the corresponding values of the data row in the target table.

CREATE TABLE triangle (
  sidea     DOUBLE,
  sideb     DOUBLE,
  sidec     DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)),
  rowdata   JSON AS (JSON_OBJECT("aa",sidea, "bb",sideb, "cc",sidec ))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);    

## Table
1   1   1.4142135623730951  {"aa": 1, "bb": 1, "cc": 1.4142135623730951}
3   4   5   {"aa": 3, "bb": 4, "cc": 5}
6   8   10  {"aa": 6, "bb": 8, "cc": 10}

Basic functions

--- Query
SELECT JSON_Array(
  'alpha'      
  ,'bravo'    
  ,'charlie'
  ,'delta'
) AS Result;          

--- Result          
["alpha", "bravo", "charlie", "delta"]

--- Query
SELECT JSON_OBJECT(
  'alpha',      'one'
  ,'bravo',     'two'
  ,'charlie',   '3'
  ,'delta',     004
) AS Result;

--- Result          
{"alpha": "one", "bravo": "two", "delta": 4, "charlie": "3"}