Currently, I am inputting JSON objects into a MySQL Database and then executing queries on them. Within the database is a table structured as follows:
subjects |
---------------------------------------------------------
|{
|"subjects": [{
| "subject":"English Language & Literature",
| "ageRange":["12-18"],
| "examlevel":["13+","gcse","as-level","a-level"],
| "examboard":["aqa","edexcel","ocr","wjec"]
|},
|{
| "subject":"Creative Writing",
| "ageRange":["8-18"], | Row 1
| "examlevel":null,
| "examboard":null
|},
|{
| "subject":"Media Studies",
| "ageRange":["14-18"],
| "examlevel":["gcse","as-level","a-level"],
| "examboard":["aqa","edexcel","ocr","wjec"]
|}]
|}
---------------------------------------------------------
In one of my MySQL queries, I executed
SELECT JSON_EXTRACT(subjects, '$."subjects"') FROM `table_name`
This returned
[{"subject": "English Language & Literature", "age...
To extract the subject name, I ran the following...
SELECT JSON_EXTRACT(subjects, '$."subjects[*]."subject"') FROM `table_name`
The result was
["English Language & Literature", "Creative Writin...
I then attempted to run a search query against the extracted JSON data
SELECT JSON_EXTRACT(subjects, JSON_UNQUOTE(JSON_SEARCH(subjects, 'all', '{Search Query i.e "E"}', NULL, '$."subjects"[*]."subject"'))) FROM `table_name`
This ^^ resulted in NULL
My goal is to dive deeper into querying. I aim to extract the subject name from each JSON object for all subjects within this group. Essentially, the search should iterate through each subject field in every JSON Object for all rows to yield results matching the search query.
Despite consulting the MySQL Docs, the information provided has not been very helpful... Now that I have obtained the Extracted JSON data, how can I perform searches against them? Furthermore, how can I selectively retrieve only those matching the search query?