I Have a data like this -
So i need to find out all rows with id=203498. How can i write a query for this ? Any Help ?
-
1Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables.user330315– user3303152021-08-30 07:31:24 +00:00Commented Aug 30, 2021 at 7:31
-
Please provide enough code so others can better understand or reproduce the problem.Community– Community Bot2021-08-31 18:15:56 +00:00Commented Aug 31, 2021 at 18:15
Add a comment
|
3 Answers
Use the JSON containment operator @>:
WHERE jsoncol @> '[ { "id": 203498 }]';
5 Comments
Stefanov.sm
[{ "id": 203498 }]
sagar
Is there any query which can update value of specific key in JSONB, based on condition ?
Sk. Irfan
Is there any way in json columns to search for multiple values like IN operator?
Laurenz Albe
@Sk.Irfan Sure, use
<@ ANY. But you cannot index that.Sk. Irfan
@LaurenzAlbe
@Query(value = "select * from conversations where meta\\:\\:jsonb @> :query", nativeQuery = true) I am using JPA query something like this and it fails, can you help? query: any(array['{"reference": {"chartId": "1"}}', '{"reference": {"chartId": "2"}}']::jsonb[])It depends your data so please add to example data but I will give you example data.
If your data like this
id | cars_info
----+------------------------------------------------------------------------------------
1 | {"id": 1, "sold": true, "brand": "Toyota", "color": ["red", "black"], "price": 285000}
2 | {"id": 2, "sold": false, "brand": "Honda", "color": ["blue", "pink"], "price": 25000}
3 | {"id": 3, "sold": true, "brand": "Mitsubishi", "color": ["black", "gray"], "price": 604520}
Your query is like this. Maybe it can occurs some errors but your query will be seem like that.
SELECT * FROM cars WHERE cars_info -> 'id' = '1';
3 Comments
Laurenz Albe
Note that there is an extra array in the data.
sagar
Here is the exaple of the data that i have -
sagar
user_id | cases abc | [ { "id": "207226", "name": null, "type": "ChangeRequest" }, { "id": "207225", "name": null, "type": "ChangeRequest" } ]
There is one more way of going in, when you are dealing with BIG JSON: you can drill down to a specific field of json (if the structure is predefined):
{
"method": "POST",
"request": {
"id": 111,
"type": "mytupe",
"date": "2000-01-01",
"amount": 2150,
},
"response": {
"message": {
"status": "success",
"confirmation_CODE": "CONFIRMATION0006229"
},
"success": true
}
}
SELECT * FROM TABLE_NAME WHERE
jsoncol -> 'request' @> ' { "id": 111 } '
OR details ->'response'->'message' @> ' { "confirmation_CODE": "CONFIRMATION0006229" } '
