Question 20662 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql how to query nested json element?

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
enter image description here

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)

Tags:

2

Answers


  1. 0

    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");
    Login or Signup to reply.
  2. 0

    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;:
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search