Using MySQL JSON field to join on a table

Question

I have a json field that stores a list of ids (not best practice here I know), I want to know if it's possible to use do operations on this JSON field and use them in the sql.

Below is a fictitious example of what I'm trying to achieve, is something like this doable?

CREATE TABLE user (
    user_id INT,
    user_name VARCHAR(50),
    user_groups JSON
);

CREATE TABLE user_group (
    user_group_id INT,
    group_name VARCHAR(50)
);

INSERT INTO user_group (user_group_id, group_name) VALUES (1, 'Group A');
INSERT INTO user_group (user_group_id, group_name) VALUES (2, 'Group B');
INSERT INTO user_group (user_group_id, group_name) VALUES (3, 'Group C');

INSERT INTO user (user_id, user_name, user_groups) VALUES (101, 'John', '[1,3]');

With the above data I would like to fashion a query that gives me the results like this:

user_id | user_name | user_group_id | group_name|
-------------------------------------------------
101     | John      | 1             | Group A
101     | John      | 3             | Group C

Some psuedo style SQL I'm thinking is below, though I still have no clue if this is possible, or what JSON functions mysql offers I would use to achieve this

 SELECT 
       u.user_id, 
       u.user_name, 
       g.user_group_id
       g.group_name
   FROM users u
   LEFT JOIN user_group g on g.user_group_id in some_json_function?(u.user_groups)

Let me know if the question isn't clear.

take a look at this https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html — Feras Salim, Oct 02 '16 at 15:07

Kyle Gobel · Accepted Answer · 2018-03-26T14:34:18.460

33

With the help of Feras's comment and some fiddling:

  SELECT 
       u.user_id, 
       u.user_name, 
       g.user_group_id,
       g.group_name
   FROM user u
   LEFT JOIN user_group g on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id as JSON), '$')

This appears to work, let me know if there's a better way.

edited Mar 26 '18 at 14:34

answered Oct 02 '16 at 15:52

Kyle Gobel

5,530
9
45
68

6

I needed to do the same thing but join on a specific value in a JSON field and the following worked for me `LEFT JOIN table b on JSON_UNQUOTE(JSON_EXTRACT(a.json_field, "$.json_value")) = b.mysql_value` – b3n Dec 01 '16 at 12:57
3

How optimized is this? – Murilo Apr 26 '17 at 13:57
Missed comma after `g.user_group_id` and original table name is `user` not `userS` – Alex G Mar 26 '18 at 02:03
This doesn't work when `user_group_id` is type `VARCHAR` – Alex G Mar 26 '18 at 02:39
4

@AlexG To get this working with a string key column user_group_id, you need to add in double quotes around the value. JSON_CONTAINS(u.user_groups, CAST(CONCAT('"',g.user_group_id,'"') as JSON), '$') – ShaunUK Apr 15 '19 at 14:31
Works like a charm. Thanks! – rodrix Sep 02 '23 at 13:51

score 7 · Answer 2 · answered Mar 05 '19 at 18:55

Funny, I got to the opposite solution compared to Kyle's.

I wrote my query like this:

SELECT 
       u.user_id, 
       u.user_name, 
       g.user_group_id,
       g.group_name
   FROM user u
   LEFT JOIN user_group g on JSON_UNQUOTE(JSON_EXTRACT(u.user_groups, '$')) = g.user_group_id;

It also works, and this solution doesn't need any transforming on the right side of the expression, this could provide a benefit in query optimizing in certain cases.

score 1 · Answer 3 · answered Jul 06 '20 at 15:41

For arrays like ["1", "2", "3"] that values are in string type, JSON_SEARCH function is the way for your question:

SELECT 
   u.user_id, 
   u.user_name, 
   g.user_group_id
   g.group_name
FROM users u
LEFT JOIN user_group g ON (JSON_SEARCH(u.user_groups, 'one', g.user_group_id))

JSON_CONTAINS function does not return true for integers as candidate parameter:

SELECT JSON_CONTAINS(CAST('["1", "2", "3"]' AS JSON), CAST(1 AS JSON), '$')

returns 0 (false). You need to change it to this:

SELECT JSON_CONTAINS(CAST('["1", "2", "3"]' AS JSON), CAST(CONCAT('"', 1, '"') AS JSON), '$')

But JSON_SEARCH can find the result:

SELECT JSON_SEARCH(CAST('["1", "2", "3"]' AS JSON), 'one', 1)

returns "$[0]" that means "true".

score 0 · Answer 4 · answered Jul 22 '21 at 10:07

I have just tried the following and it worked in mysql 8.0.26:

-- index
ALTER TABLE user ADD KEY ( (CAST(user_groups -> '$' AS UNSIGNED ARRAY)) );

SELECT * 
FROM user as a
left join user_group as t on t.user_group_id MEMBER OF (a.user_groups)
where t.group_name in ('Group A', 'Group C');

The query is based on the following select syntax:

SELECT * FROM t_json_arrays WHERE 3 MEMBER OF (c_array);

See examples on: https://saveriomiroddi.github.io/Storage-and-indexed-access-of-denormalized-columns-arrays-on-mysql-8.0-via-multi-valued-indexes/

score 0 · Answer 5 · edited Jun 28 '23 at 12:55

0

On maria DB 10:

SELECT 
    u.user_id, 
    u.user_name, 
    g.user_group_id,
    g.group_name
FROM user u
JOIN user_group g 
on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id AS CHAR));

edited Jun 28 '23 at 12:55

General Grievance

4,555
31
31
45

answered Jun 28 '23 at 11:32

Shinhyun Min

1
1

Shinhyun Min · Answer 6 · 2023-06-30T01:13:00.633

0

I found another solution when we use "string (char)" type array elements.(It works on MariaDB 11)

SELECT 
    u.user_id, 
    u.user_name, 
    g.user_group_id,
    g.group_name
FROM user u
JOIN user_group g 
on JSON_CONTAINS(u.user_groups, concat('"',g.user_group_id AS CHAR,'"'));

the key point is

concat(u.user_groups, concat('"',g.user_group_id AS CHAR,'"'));

edited Jun 30 '23 at 01:13

answered Jun 30 '23 at 01:12

Shinhyun Min

1
1

Using MySQL JSON field to join on a table

6 Answers6

Linked

Related