so here is the schema for the JSON :
{
"ts": 1677330162,
"uuid": "75e4917d-69cd-49e1-963f-380ea031a88b",
"moderators": [
{
"userId": "000001",
"timeAdded": 1677332262,
"permissions": {
"all": true,
"tag": true,
"mail": true,
"posts": true,
"access": true,
"config": true,
"analytics": true
}
}
],
"lastUpdated": 1677332262
}
i have tried all of these queries:
Query a JSON column with an array of object in MySQL
and nothing is working, mysql doesnt give an error, just no results.
my last query i tried:
SELECT *
FROM community__sharded
WHERE data->'$.moderators.*.userId' = json_array(000001);
i have no idea where im going wrong, can i get some help?
update:
my end goal is to get the JSON key "userId:xx", but the moderators key is an array, so i cant just do moderators.userId:xx,
solved below (sorry for lack of info in the beginning, was half asleep)
2
Answers
json_array(000001) will output [1] not [000001]
So it must be casted to varchar
Try using JSON_SEARCH:
Quoting the docs: