MariaDB og JSON data

mysql> CREATE TABLE mik (
    ->    id INTEGER NOT NULL,
    ->    doc JSON,
    ->    PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO mik VALUES(1, '{}');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT doc FROM mik WHERE id = 1;
+------+
| doc  |
+------+
| {}  |
+------+
1 row in set (0.00 sec)

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.a', 123);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT doc FROM mik WHERE id = 1;
+------------+
| doc        |
+------------+
| {"a": 123} |
+------------+
1 row in set (0.00 sec)

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.b', 'ABC');
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT doc FROM mik WHERE id = 1;
+------------------------+
| doc                    |
+------------------------+
| {"a": 123, "b": "ABC"} |
+------------------------+
1 row in set (0.00 sec)

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.c', JSON_ARRAY(123, 456));
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT doc FROM mik WHERE id = 1;
+-----------------------------------------+
| doc                                    |
+-----------------------------------------+
| {"a": 123, "b": "ABC", "c": [123, 456]} |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.d', JSON_ARRAY('ABC', 'DEF'));
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT doc FROM mik WHERE id = 1;
+--------------------------------------------------------------+
| doc                                                          |
+--------------------------------------------------------------+
| {"a": 123, "b": "ABC", "c": [123, 456], "d": ["ABC", "DEF"]} |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.e', JSON_OBJECT('foo', 123, 'bar',
'ABC'));
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT doc FROM mik WHERE id = 1;
+-----------------------------------------------------------------------------------------------+
| doc                                                                                          |
+-----------------------------------------------------------------------------------------------+
| {"a": 123, "b": "ABC", "c": [123, 456], "d": ["ABC", "DEF"], "e": {"bar": "ABC", "foo": 123}} |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE mik;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE mik (
    ->    id INTEGER NOT NULL,
    ->    doc JSON,
    ->    PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mik VALUES(1, '{}');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT doc FROM mik WHERE id = 1;
+------+
| doc  |
+------+
| {}  |
+------+
1 row in set (0.00 sec)

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.a', 123) WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT doc FROM mik WHERE id = 1;
+------------+
| doc        |
+------------+
| {"a": 123} |
+------------+
1 row in set (0.00 sec)

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.b', 'ABC') WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT doc FROM mik WHERE id = 1;
+------------------------+
| doc                    |
+------------------------+
| {"a": 123, "b": "ABC"} |
+------------------------+
1 row in set (0.00 sec)

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.c', '{}') WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT doc FROM mik WHERE id = 1;
+-----------------------------------+
| doc                              |
+-----------------------------------+
| {"a": 123, "b": "ABC", "c": "{}"} |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.c',JSON_SET(doc->>'$.c', '$.c1', JSON_OBJECT())) WHERE id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.c', JSON_SET(doc->>'$.c', '$.c1', JSON_SET(doc->>'$.c.c1', '$.c1x', JSON_OBJECT('x', 1)))) WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.c', JSON_SET(doc->>'$.c', '$.c1', JSON_SET(doc->>'$.c.c1', '$.c1y', JSON_OBJECT('y', 2)))) WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.c',JSON_SET(doc->>'$.c', '$.c2', JSON_OBJECT())) WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.c', JSON_SET(doc->>'$.c', '$.c2', JSON_SET(doc->>'$.c.c2', '$.c2x', JSON_OBJECT('x', 3)))) WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE mik SET doc = JSON_SET(doc, '$.c', JSON_SET(doc->>'$.c', '$.c2', JSON_SET(doc->>'$.c.c2', '$.c2y', JSON_OBJECT('y', 4)))) WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT doc FROM mik WHERE id = 1;
+-------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------+
| {"a": 123, "b": "ABC", "c": {"c1": {"c1x": {"x": 1}, "c1y": {"y": 2}}, "c2": {"c2x": {"x": 3}, "c2y": {"y": 4}}}} |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT doc->>'$.c.c1.c1x',doc->>'$.c.c2.c2y' FROM mik;
+--------------------+--------------------+
| doc->>'$.c.c1.c1x' | doc->>'$.c.c2.c2y' |
+--------------------+--------------------+
| {"x": 1}          | {"y": 4}          |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE mik;
Query OK, 0 rows affected (0.01 sec)

<?php
$fromdb = '{"a": 123, "b": "ABC"}';
echo $fromdb . "\r\n";
$o = json_decode($fromdb, true);
$o['c'] = array('c1' => array('c1x' => array('x' => 1), 'c1y' => array('y' => 2)), 'c2' => array('c2x' => array('x' => 3), 'c2y' => array('y' => 4)));
$todb = json_encode($o);
echo $todb . "\r\n";
?>

{"a": 123, "b": "ABC"}
{"a":123,"b":"ABC","c":{"c1":{"c1x":{"x":1},"c1y":{"y":2}},"c2":{"c2x":{"x":3},"c2y":{"y":4}}}}