Building JSON

Building JSON

Constructing Objects and Arrays

JSON_OBJECT(key, value, ...) and JSON_ARRAY(value, ...) build documents from SQL values and map the types: JSON_OBJECT('sku', sku, 'price', price, 'in_stock', stock > 0) on the out-of-stock ebook gave {"sku": "BK-SQL-02", "price": 29.00, "in_stock": false}, with the DECIMAL exact and the comparison a real boolean. JSON_ARRAY_APPEND() adds to the end of an array and JSON_ARRAY_INSERT() at a position such as '$.tags[0]'. The trap is passing JSON as a plain string: JSON_SET(attributes, '$.tags', '["mysql", "sql"]') stores one string, which JSON_TYPE() reports as STRING. Wrap such text in CAST(... AS JSON), as the listing does when it gives the books the tags arrays that the rest of this section searches, flattens and indexes.

Tagging the books with arraysSQL

UPDATE products p
  JOIN (VALUES ROW(1, '["php", "web"]'), ROW(2, '["mysql", "sql"]'),
               ROW(3, '["mysql", "indexing"]'), ROW(4, '["php", "laravel", "web"]'),
               ROW(6, '["linux", "server"]')) AS t (id, tags) ON t.id = p.id
   SET p.attributes = JSON_SET(p.attributes, '$.tags', CAST(t.tags AS JSON));
UPDATE products SET attributes = JSON_ARRAY_APPEND(attributes, '$.tags', 'best-seller')
 WHERE id = 4;
SELECT attributes FROM products WHERE id = 4;

Output

+-----------------------------------------------------------------------------------------+
| attributes                                                                              |
+-----------------------------------------------------------------------------------------+
| {"tags": ["php", "laravel", "web", "best-seller"], "pages": 520, "format": "hardcover"} |
+-----------------------------------------------------------------------------------------+

To turn a whole result set into one array, use the aggregates JSON_ARRAYAGG() and JSON_OBJECTAGG() from GROUP_CONCAT and JSON, and PHP then needs a single json_decode() (PHP).

MENU