Searching JSON

Searching JSON

Searching, Merging, and Inspecting JSON

value MEMBER OF (array) tests one element, JSON_CONTAINS(target, candidate) requires every candidate element, JSON_OVERLAPS(a, b) requires any shared element, and JSON_CONTAINS_PATH(doc, 'one'|'all', path, ...) tests for keys. The first three are the predicates a multi-valued index can serve (Indexing JSON). Mind the NULLs: a product without tags is not "not tagged php" but unknown.

Array predicates and inspection functionsSQL

SELECT sku, 'php' MEMBER OF (attributes->'$.tags')              AS php,
       JSON_CONTAINS(attributes->'$.tags', '["php", "web"]')       AS both_,
       JSON_OVERLAPS(attributes->'$.tags', '["sql", "web"]')       AS either,
       JSON_CONTAINS_PATH(attributes, 'one', '$.color', '$.count') AS merch,
       JSON_LENGTH(attributes, '$.tags') AS n_tags, JSON_KEYS(attributes) AS `keys`
  FROM products WHERE id IN (4, 5, 7);

Output

+-----------+------+-------+--------+-------+--------+-----------------------------+
| sku       | php  | both_ | either | merch | n_tags | keys                        |
+-----------+------+-------+--------+-------+--------+-----------------------------+
| BK-LAR-01 |    1 |     1 |      1 |     0 |      4 | ["tags", "pages", "format"] |
| BK-UX-01  | NULL |  NULL |   NULL |     0 |   NULL | ["pages", "format"]         |
| AC-MUG-01 | NULL |  NULL |   NULL |     1 |   NULL | ["color", "capacity_ml"]    |
+-----------+------+-------+--------+-------+--------+-----------------------------+

JSON_SEARCH(attributes, 'one', 'lin%') finds where a string sits: "$.tags[0]" on the Linux book. JSON_MERGE_PATCH() follows RFC 7396 (HTTP PATCH): later values win, arrays are replaced, and null deletes a key, so merging {"pages": 188, "tags": ["mysql"], "format": "ebook"} with {"pages": 196, "tags": ["sql"], "format": null} gave {"tags": ["sql"], "pages": 196}. JSON_MERGE_PRESERVE() keeps both sides, giving "pages": [188, 196]. The old JSON_MERGE() raises deprecation warning 1287.

MENU