Filter Nested JSON in PostgreSQL DB-CodePudding

{
"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.

CodePudding user response：

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

CodePudding user response：

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