JSON type in MySQL

mysql-json.md

JSON type in MySQL

MySQL 5.7 comes with a native JSON data type and a set of built-in functions to manipulate values of the JSON type. This document demonstrates the usage.

INSERT

-- raw json
INSERT INTO json_test 
  (name, attributes)
VALUES (
  'some name',
  '{"key1": "val1", "key2": {"key2-1": 0, "key2-2": 55}, "key3": ["val1", "val2", "val3"]}'
);

-- same values using JSON_OBJECT, JSON_MERGE and JSON_ARRAY
INSERT INTO json_test 
  (name, attributes)
VALUES (
  'some name',
  JSON_OBJECT(
    'key1',
    'val1',
    'key2',
    JSON_MERGE(
      '{"key2-1": 0}',
      '{"key2-2": 55}'
    ),
    'key3',
    JSON_ARRAY(
      'val1', 'val2', 'val3'
    )
  )
);

-- using JSON_MERGE with the same key -> array
-- output: {"key1": ["val1", "val2", "val3", "val4"]}
SELECT JSON_MERGE(
  '{"key1": "val1"}',
  '{"key1": "val2"}',
  '{"key1": "val3"}',
  '{"key1": "val4"}'
);

-- IMPORTANT: JSON_OBJECT will ignore duplicate keys
-- output: {"key1": 1, "key2": "abc"}
SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def')

SELECT

SELECT *
FROM json_test
WHERE
--  note `key2-2` wrapped in extra quotes
--  path components that name keys must be quoted if the unquoted key name is not legal in path expressions 
--  (https://stackoverflow.com/questions/35735454/mysql-json-extract-path-expression-error)
  JSON_EXTRACT(attributes, '$.key2."key2-2"') > 50 
--   column -> path notation is shorthand for JSON_EXTRACT
  AND attributes -> '$.key1' = 'val1'

-- JSON_CONTAINS_PATH one - OR, needs to match only one path
SELECT * 
FROM json_test 
WHERE 
  JSON_CONTAINS_PATH(attributes, 'one', '$.key2."key2-1"');

-- JSON_CONTAINS_PATH all - AND, must match all paths
SELECT * 
FROM json_test 
WHERE 
  JSON_CONTAINS_PATH(attributes, 'one', '$.key1', '$.key2."key2-1"');

-- JSON_CONTAINS
SELECT *
FROM json_test
WHERE
  -- notice number in quotes
  JSON_CONTAINS(attributes, '55', '$.key2."key2-2"');

-- value (object content) can be partial
SELECT * 
FROM json_test 
WHERE
  JSON_CONTAINS(attributes, '{"key2-1": "some string"}', '$.key2');

-- JSON_SEARCH
-- finds the path given the value (slow)
-- all - returns all paths, one - returns the first one
SELECT JSON_SEARCH(attributes, 'all', 'some string')
FROM json_test;

-- JSON_KEYS
-- returns an array of keys
SELECT *, JSON_KEYS(attributes)
FROM json_test
WHERE 
  JSON_KEYS(attributes) = JSON_ARRAY('key1', 'key2', 'key3');

UPDATE

UPDATE json_test
SET attributes = JSON_REPLACE(
  attributes,
  '$.key2."key2-1"',
  'some string'
)
WHERE
  attributes -> '$.key2."key2-2"' = 55;

-- JSON_INSERT - only add prop if it DOES NOT exist
-- JSON_REPLACE - only replace (it it DOES exist)
-- JSON_SET - add (if it does not exist) or replace it (if it exists)

-- remove key/value 
UPDATE json_test
SET attributes = JSON_REMOVE(attributes , '$.key3')

Additional resources:

• http://rpbouman.blogspot.com/2015/11/mysql-few-observations-on-json-type.html
• https://dev.mysql.com/doc/refman/5.7/en/json-functions.html
• https://benjaminlistwon.com/blog/working-with-json-data-in-mysql-part-2-of-3/