I have a json data type field in mysql 8.0.32 that has data like this:
{
"0": {
"userID": 123,
"name": "Name1",
"age ": 20,
"district": 5,
"phone": 0,
},
"1": {
"userID": 124,
"name": "Name2",
"age ": 30,
"district": 5
"cell": 0,
},
"2": {
"userID": 125,
"name": "Name3",
"age ": 40,
"district": 10,
"relatedIDs": [50, 51]
}
}
Sample create table:
CREATE TABLE ` json_table` (
`id` int NOT NULL AUTO_INCREMENT,
` json_column` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Every entry represents a "user" and has a userID. Only the userID property is guaranteed to exist, other fields can vary from user to user. This is a single json document, with one per row in this column.
How do I query all of the properties for a single user based on the userID? How would I query for multiple users via multiple userIDs? I must only query the contents of this particular row, not every json document in the column. I do not know the key of the sub object ahead of time (0,1,2).
This is as far as I have made it:
SELECT json_column FROM json_table WHERE id = 1 AND JSON_CONTAINS(json_column '["userID"]');
The result should look something like this:
# preferred result, but if the other way is more efficient, I can work with it
{
"userID": 123,
"name": "Name1",
"age ": 20,
"district": 5,
"phone": 0,
}
# or:
"0": {
"userID": 123,
"name": "Name1",
"age ": 20,
"district": 5,
"phone": 0,
}
This has to be done in a json column. I can't convert it. But I can change the JSON format, if there is a better way to organize it to make these queries. For instance I could make the json document an array of objects, or the key the userID, or anything else.
I tried the answer in this somewhat similar post How to search nested JSON in MySQL but its content is an array of objects, not an object of objects. There is also syntax error in the accepted answer and it was for the older version of mysql 5.7. Mysql 8 has had several improvements and changes to its json functions.
