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? dev.mysql.com/doc/refman/8.0/en/json-search-functions.html — protob
– protob, Commented Feb 25, 2023 at 15:20

SelVazi · Accepted Answer · 2023-02-25 15:19:29Z

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, 2023 at 15:19

SelVazi

16.2k2 gold badges19 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

protob · Accepted Answer · 2023-02-25 15:23:45Z

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;:

answered Feb 25, 2023 at 15:23

protob

3,6211 gold badge11 silver badges24 bronze badges

Comments

bwhyman · Accepted Answer · 2023-10-24 04:01:02Z

0

select * from community__sharded cs
join json_table(
    cs.data ->> '$.moderators',
    '$[*]'
    columns (
        `id` for ordinality ,
        `userId` varchar(10) path '$.userId'
        )
    ) as mo 
where mo.userId='00001'

answered Oct 24, 2023 at 4:01

bwhyman

726 bronze badges

Collectives™ on Stack Overflow

mysql how to query nested json element?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related