CODEWITHSUNDEEP
sqlarraysjsonmariadb
mcmacerson
mcmacerson

Reputation: 933

How do you use a JSON array in a "WHERE IN" clause in MariaDB

I am using MariaDB version 10.2

I have an array of GUIDs stored as JSON in a table and I want to select rows from another table where the ID matches any of the GUIDs in the JSON array.

group_table:
id       json_data
--       ---------
23       ["69CF6C989F3942889305249573D1A08D","00E534B77C9A481596BB84947A58F7A4"]

child_table:
child_id                            some_data
--------                            ---------
69CF6C989F3942889305249573D1A08D    child one
00E534B77C9A481596BB84947A58F7A4    child two

What I want to do is:

SELECT * from child_table where child_id in (select json_data from group_table where id = 23)

Upvotes: 0

Views: 594

Answers (2)

manno
manno

Reputation: 111

Modifying Pilosa's answer I modified it to run a search on JSON Object Keys. So if we modified the example to have the following tables:

store_table:
-----------------------------------------------------------
store_id       store_stock
--------       ---------
23             {"stock": {"apple":"73", "banana":"pieces"}}
24             {"stock": {"pear":"28", "plum":"52"}}

fruit_table:
-----------------------------------------------------------
fruit_type                          fruit_metric
--------                            --------------
apple                               pounds
plum                                pounds
banana                              units
pear                                pounds
pineapple                           units

Note the curly brackets { }. We can select pricing_metric from the fruit_table based on the json_data in the store_table like so:

SELECT 
    fruit_type, 
    fruit_metric
FROM
    stride.fruit_table
WHERE
    JSON_SEARCH(
        (
            SELECT JSON_KEYS(store_stock, '$.stock')
            FROM store_table 
            WHERE store_id = 1  
        ),
        'one',
        fruit_type
    ) IS NOT NULL;

This would then return:

+----------------------------+
| fruit_type| fruit_metric   |
+----------------------------+
| apple     | pounds         |
| banana    | units          |
+----------------------------+

Upvotes: 1

Steffen Bobek
Steffen Bobek

Reputation: 642

Are you looking for JSON_SEARCH?

select
    *
from
    child_table
where
    json_search((select json_data from group_table where id = 23), 'one', child_id) is not null

Upvotes: 1

Related Questions