By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (metadata JSON)
SELECT '[{"key": "field_1", "value": "test_1"}, {"key": "field_2", "value": "test_2"}]' metadata
UNION ALL
SELECT '[{"key": "field_1", "value": "1"}, {"key": "field_2", "value": "test_2"}]'
UNION ALL
SELECT '[{"key": "field_2", "value": "test_2"}, {"key": "field_1", "value": "test_1"}]';
SELECT CAST(metadata AS CHAR) FROM test;
Records: 3 Duplicates: 0 Warnings: 0
| CAST(metadata AS CHAR) |
|---|
| [{"key": "field_1", "value": "test_1"}, {"key": "field_2", "value": "test_2"}] |
| [{"key": "field_1", "value": "1"}, {"key": "field_2", "value": "test_2"}] |
| [{"key": "field_2", "value": "test_2"}, {"key": "field_1", "value": "test_1"}] |
SELECT CAST(metadata AS CHAR)
FROM test
CROSS JOIN JSON_TABLE(test.metadata,
'$[*]' COLUMNS (`key` TEXT PATH '$.key',
`value` TEXT PATH '$.value')) jsontable
WHERE jsontable.key = 'field_1'
AND jsontable.value LIKE '%est%';
| CAST(metadata AS CHAR) |
|---|
| [{"key": "field_1", "value": "test_1"}, {"key": "field_2", "value": "test_2"}] |
| [{"key": "field_2", "value": "test_2"}, {"key": "field_1", "value": "test_1"}] |