I have the following table and json values: database is 'myusers'
column username column jsonstuff
database is 'myusers'
column username column jsonstuff
user1 {'id' : 'user1', 'phones' : [{'id': 'x678', 'brand' : 'apple'},{'id': 'y123', 'brand' : 'samsung'},{'id': 'c458', 'brand' : 'sony'}]}
user2 {'id' : 'user2', 'phones' : [{'id': 'x356', 'brand' : 'apple'},{'id': 'y732', 'brand' : 'samsung'}]}
I want to query all users in the database that have an 'apple' phone, then delete that 'apple' phone from the json array. So the resulting table would look like:
user1 {'id' : 'user1', 'phones' : [{'id': 'y123', 'brand' : 'samsung'},{'id': 'c458', 'brand' : 'sony'}]}
user2 {'id' : 'user2', 'phones' : [{'id': 'y732', 'brand' : 'samsung'}]}
Can this be done in a single query, or do i need a looping function? For just getting the selection, i have tried:
select json_query(jsonstuff, '$.phones[*]' with wrapper) from myusers where 'brand' = 'apple';
With with no results. It looks like i would need a nested loop or nested query, but not sure how to even start.
