mysql how to query nested json element?

Question

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
}

which is stored as

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)

Try using `JSON search function`? https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html — protob, Feb 25 '23 at 15:20

score 1 · Accepted Answer · answered Feb 25 '23 at 15:19

1

json_array(000001) will output [1] not [000001]

So it must be casted to varchar

SELECT * FROM community__sharded 
WHERE data->'$.moderators[*].userId' = json_array("000001");

answered Feb 25 '23 at 15:19

SelVazi

10,028
2
13
29

score 0 · Answer 2 · answered Feb 25 '23 at 15:23

Try using JSON_SEARCH:

SELECT *
FROM community__sharded
WHERE JSON_SEARCH(data, 'one', '000001', NULL, '$.moderators[*].userId') IS NOT NULL;

Quoting the docs:

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

Returns the path to the given string within a JSON document. Returns NULL if any of the json_doc, search_str, or path arguments are NULL;:

mysql how to query nested json element?

2 Answers2