Questions about mysql json_extract()

Question

Hello i have a question about mysql json_extract function.

there are two tables;

users
+------------------------------------+
|   id  |  email                     |
+------------------------------------+
|   1   |  [email protected]            |      
+------------------------------------+
|   2   |  [email protected]          |      
+------------------------------------+

room
+----------------------------------------------------------------------------------+
|   id  |  invited(json type)                                                      |                                          
+----------------------------------------------------------------------------------+
|   1   |  {"invite": [{"To": "[email protected]"}, {"To": "[email protected]"}]}    
+----------------------------------------------------------------------------------+

i want to get invited users from room table. so i tried

SELECT A.id, A.email
FROM users A JOIN room B ON 
(A.email = json_extract(B.invited,'$.invite[*].To')

but json_extract() return

["[email protected]", "[email protected]"]

If I use 'json_extract(B.invited,'$.invite[0].To')'
it is possible to get one. but can't get several users.

is there any possible way?

Paul Spiegel · Accepted Answer · 2018-04-18 13:44:52Z

1

Try it with JSON_CONTAINS. It should be something like that:

SELECT A.id, A.email
FROM users A
JOIN room B 
  ON json_contains(json_extract(B.invited,'$.invite[*].To'), json_quote(A.email))

answered Apr 18, 2018 at 13:44

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Questions about mysql json_extract()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related