mysql mysql how to query nested json element?

[QQ_cazo]

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:

https://stackoverflow.com/questions/38797627/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?

[Guest]

Are you trying to select something based on a specific value within the Json? 

 

But not based on matching an entire Json file

[QQ_cazo]

4 hours ago, fpo said:

Are you trying to select something based on a specific value within the Json? 

 

But not based on matching an entire Json file

yes I think so, im only looking for the json that has moderators (array/list ) userid and that user id must match 00001

[Guest]

58 minutes ago, QQ_cazo said:

yes I think so, im only looking for the json that has moderators (array/list ) userid and that user id must match 00001

Why are you using Json instead of another table? 

 

I honestly didn't know SQL supported Json specifically. 

It's possible your project is out of your hands for deciding Json vs table. 

 

However... If it's not, I think it's common practice to make a new table or columns. 

[Toxocious]

I'm honestly not sure if what you're trying to do here is possible.

 

I'd argue that, in the case that you want to store JSON in your database - which is fine - to retrieve it in your language of choice and parse the results of the query if applicable.

[C2dan88]

17 hours ago, QQ_cazo said:

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?

Your json path is incorrect. The "moderators" field is an array. So the path will be $.moderators[*].userId

 

Never used json in mysql before. This is what I came up with.

SELECT * FROM community__sharded WHERE JSON_VALUE(data, '$.moderators[*].userId') = 1;

 

[Eigenvektor]

8 hours ago, fpo said:

However... If it's not, I think it's common practice to make a new table or columns. 

Agreed. For performance it would likely be much better to store the values in the database in separate tables/columns. This way you can use regular where clauses, joins etc. Then use an object mapper to convert query results to objects and/or JSON as/when needed.

[Guest]

25 minutes ago, Eigenvektor said:

Agreed. For performance it would likely be much better to store the values in the database in separate tables/columns. This way you can use regular where clauses, joins etc. Then use an object mapper to convert query results to objects and/or JSON as/when needed.

Incase it's out of his control, do you know of any way to select data based on contents of a Json column? 

[Eigenvektor]

15 minutes ago, fpo said:

Incase it's out of his control, do you know of any way to select data based on contents of a Json column? 

Unfortunately, I don't. For simple use cases you can probably get away with a LIKE-clause. Or you can use LOCATE in combination with SUBSTRING, to search in a particular part of the string.

 

Something like

WHERE SUBSTRING(data, LOCATE('userId', data) + 3, 6) = '000001'

Of course that will fail if your JSON isn't consistently formatted

[QQ_cazo]

sorry i forgot i made this, my problem was a INT vs a STRING.... mysql wasnt able to find the INT due to i had it set in the json as a string. Didnt know mysql had strict types like that, but hey, cool!

[CottonUntwist]

If you plan on using a lot of json in your tables I'd switch to postgresql (actually I'd switch regardless) and use the jsonb column type which will change your json to binary json inside the table... this allows queries, indexing etc on any of the values/keys there.

[QQ_cazo]

On 3/13/2023 at 5:21 PM, CottonUntwist said:

If you plan on using a lot of json in your tables I'd switch to postgresql (actually I'd switch regardless) and use the jsonb column type which will change your json to binary json inside the table... this allows queries, indexing etc on any of the values/keys there.

for postgres, since im using python to interface with mysql, which library would you recommend to interface with postgres?

[CottonUntwist]

9 hours ago, QQ_cazo said:

for postgres, since im using python to interface with mysql, which library would you recommend to interface with postgres?

I don't use python but aren't nearly all databases through https://www.sqlalchemy.org/ ?