JSONB in where clause postgres

Question

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 ?

Pargeting

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. – Donell

Please provide enough code so others can better understand or reproduce the problem. – Forefinger

Answer 1

Use the JSON containment operator @>:

WHERE jsoncol @> '[ { "id": 203498 }]';

Pin

[{ "id": 203498 }] – Obstacle

Is there any query which can update value of specific key in JSONB, based on condition ? – Pargeting

Is there any way in json columns to search for multiple values like IN operator? – Lincoln

@Sk.Irfan Sure, use <@ ANY. But you cannot index that. – Pin

@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[]) – Lincoln

Answer 2

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

Publius

Note that there is an extra array in the data. – Pin

Here is the exaple of the data that i have - – Pargeting

user_id | cases abc | [ { "id": "207226", "name": null, "type": "ChangeRequest" }, { "id": "207225", "name": null, "type": "ChangeRequest" } ] – Pargeting

Answer 3

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" } '

Fulviah