How to filter data through WHERE json statement

structure of column is> SELECT "startedByAttendanceEntry" FROM "attendance_block"

[
  {
    person: {
      firstName: "Koste";
      lastName: "Litaci";
    };
  }
  ...
]

Data type is json, I can't change type or any of the structure of db

What I want to achieve is to select only elements with person firstName = Koste AND lastName = Litaci

what I tried

SELECT "startedByAttendanceEntry" 
FROM "attendance_block" 
WHERE 'person' ->> 'lastName' = 'Litaci' 
  AND 'person' ->> 'firstName' = 'Koste'

and many more all end it with err saying driverError: error: operator is not unique: unknown ->> unknown

CodePudding user response：

Well, 'person' is a varchar/text constant, and thus you cannot apply a JSON operator on it. Additionally your JSON contains an array, so access through ->> won't work either because you need to specify the array index.

You can use the contains operator @> to find a specific key/value pair in the array. As you chose to not use the recommended jsonb type, you need to cast the column:

where "startedByAttendanceEntry"::jsonb @> '[{"person":{"firstName": "Koste", "lastName": "Litaci"}}]'

TOPIC AUTHOR EDIT:

all the code to work was as follow

SELECT "startedByAttendanceEntry" 
FROM "attendance_block" 
WHERE "startedByAttendanceEntry"::jsonb @> '{"person":{"firstName": "Koste", "lastName": "Litaci"}}'