'mysql json get data where key in json is a value from another table, pure sql code

There is a table tbl1 with json data:

(id int autoincrement primary key unique, json_data json)

tbl1

id json_data
1 {"john":{"3":"56"}}
2 ......

There is a table tbl2 with regular data like this:

(id int autoincrement primary key, student varchar(45) unique, year int(3) unsigned)

tbl2

id student year
1 john 3
2 ...... ...

If I need to get data from tbl1, I can execute this query:

select BB.json_data->>'$."john"."3"'from tbl1 BB

RESULT: 56

But, if I need to get data from tbl1 based on data in table tbl2, HOW CAN WE CREATE QUERY TO GET 56 AS DATA FROM TBL1 FROM JSON_DATA COLUMN:

select (select BB.json_data->>'$."AA.student"."AA.year"' from tbl1 BB) from tbl2 AA WHERE AA.student = 'john' LIMIT 1

RESULT MUST BE: 56

For better understanding!

As you can see, json data in tbl1 are parts of data that we can find in tbl2.

In tbl2:

student: john, year:3

It means in tbl1:

json_data == {"john":{"3":"56"}}

or in general json_data is in this format:

{"student_name":{"study_year":"data"}}

So, I need to get this data, if I know student_name and study_year which can be dinamicaly changed:

select BB.json_data->>'$."john"."3"'from tbl1 BB

RESULT: 56

mysqljson


Solution 1:[1]

SELECT *, 
       JSON_EXTRACT(tbl1.json_data, 
                    CONCAT_WS('.', '$', tbl2.student, tbl2.`year`))
FROM tbl1
JOIN tbl2 USING (id)

If student may contain a char which needs in quoting while used in JSON path (space char, for example) then wrap its value with double quotes.

Solution 2:[2]

Thanks to Akina this query is also one of many possible solutions.

select (select

JSON_EXTRACT(BB.json_data, CONCAT_WS('.', '$', AA.student, AA.year))

from tab1 BB) from tab2 AA

Solution 3:[3]

With this similar query, you can get you data:

select (select

JSON_EXTRACT(BB.json_data, CONCAT_WS('.', '$', AA.student, AA.year))

from tab1 BB) from tab2 AA

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Akina
Solution 2
Solution 3 Ignjatije

Related Questions