How to filter data through WHERE json statement - postgres

Question

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

score 2 · Accepted Answer · 2022-11-01 21:13:51Z

2

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

edited Nov 1, 2022 at 21:13

answered Nov 1, 2022 at 19:30

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

x-magix Over a year ago

perfect, thank you, I had to just little edit it but it is what was expected

user330315 Over a year ago

@x-magix If your edit solved the question, then the data in your table does not match the data in your question.

Collectives™ on Stack Overflow

How to filter data through WHERE json statement - postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related