Select all rows where key in JSON key value array

Question

Okay, so I just found out that you can save and select on the JSON data type in MySQL.

I tried to follow user2458995's answer to a similar question as mine, (The difference is mine is with a key and the value is an array) I copied his exact code, created the table, put in the data from his insert SQL and tried his select, which gave me an error.

I modified the table here to show a sample of how the data will be stored in my example.

Basically I have this setup

CREATE TABLE my_table (id INT, data JSON);

INSERT INTO my_table VALUES (1, '{"hashtags": ["html", "php", "javascript"], "categories": [2,5,6]}'), (2, '{"hashtags": ["css", "jquery", "html"], "categories": [2,5,6]}')

If I want to select all the hashtags I can do it like this

SELECT data->>"$.hashtags" FROM my_table

But how do I select data like a where in?

I imagined something like this

SELECT * FROM `my_table` WHERE 'html' IN (data->>"$.hashtags")

It does execute, but it returns no rows. I also tried several other suggested ways but I can't get anything to work

SELECT * FROM `my_table` WHERE JSON_CONTAINS('html', '$.hashtags')

I thought this would be a really neat approach, but would it be smarter to just store each category/hashtag in a unique row with an FK id?

I hope someone can help me out here :)

Which version of MySQL are you using? – GMB Oct 20 '19 at 19:48 — GMB, Oct 20 '19 at 19:48
My MySQL version is 5.7.27 – ii iml0sto1 Oct 20 '19 at 20:07 — ii iml0sto1, Oct 20 '19 at 20:07

GMB · Accepted Answer · 2019-10-20T20:08:01.260

You could adress this with JSON_SEARCH(), available in MySQL 5.7:

select *
from `my_table` 
where json_search(data, 'one', 'html', null, '$.hashtags[*]') is not null

Explanation:

json_search(
    data,            -- json document to search
    'one',           -- the search terminates after the first match 
    'html',          -- search string argument
    null,            -- escape character: none
    '$.hashtags[*]'  -- path to search: the json array under attribyte 'hashtag'
)

Demo on MySQL 5.7 DB Fiddle:

select 
    t.*,
    json_search(data, 'one', 'html', null, '$.hashtags[*]') matched_path
from `my_table` t 
where json_search(data, 'one', 'html', null, '$.hashtags[*]') is not null;

| id  | data                                                                 | matched_path    |
| --- | -------------------------------------------------------------------- | --------------- |
| 1   | {"hashtags": ["html", "php", "javascript"], "categories": [2, 5, 6]} | "$.hashtags[0]" |
| 2   | {"hashtags": ["css", "jquery", "html"], "categories": [2, 5, 6]}     | "$.hashtags[2]" |

This worked like a charm! :) should I look into upgrading to MySQL version 8? Also what are your thoughts, is it a bad way to store the data for hashtags and categories, instead of single rows with FK ids:)? — ii iml0sto1, Oct 20 '19 at 20:07
is there a way I can search for multiple values? like the where in, if I etch want all the results where etc 'html' and 'php' are presented? — ii iml0sto1, Oct 21 '19 at 18:20

Select all rows where key in JSON key value array

1 Answers1