Question 90835 in PostgreSQL Question posted in
The official documentation can be found here.

Filter Nested JSON in PostgreSQL DB

{
"List1": [
    {
      "f1": "b6ff",
      "f2": "day",
      "f3": "HO",
      "List2": [{"f1": 1.5,"f2": "RATE"}]
    }]
}

This is nested JSON in which there’s a list ‘List2’ inside another list ‘List1’.

how to filter f1 = 1.5 in List2? I have tried using @> operator used for contains but it doesn’t work with nested JSON.

Tags:

2

Answers


  1. 0

    You can try this in Where clause to fetch the whole record and then use Python to get that element.

    SELECT .... WHERE 
dbfieldname #> {'List1', '0', 'List2', '0', 'f1'} = 1.5
    Login or Signup to reply.
  2. 0

    Assuming you are using an up-to-date Postgres version and you want to get the rows that fulfill the condition, you can use a JSON path expression:

    select *
from the_table
where the_column @@ '$.List1[*].List2[*].f1 == 1.5'

    Alternatively you can use the @> operator, but the parameter must match the array structure in the column:

    where the_column @> '{"List1":[{"List2":[{"f1": 1.5}]}]}';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search