Trying to filter a SQL query on a JSON field

I have 4 tables in PostgreSQL:

HomeSearch(id, client_id, name)  HomeSearchNote(id, homesearch_id, text)  Client(id, user_id)  User(id)  

I'm trying to query HomeSearch and return a JSON as follows but only for those entries in HomeSearch for which the Client.user_id is equal to a certain value (say, 100):

HomeSearch {    id:    name:    client: {      id:      user: {        id      }    }    notes: [{      id:      homesearch_id:      text:    },    ...]  }  

My SQL statement is:

SELECT    *,   ( SELECT row_to_json(client) from client where homeSearch.client_id=client.id ) client,   ( SELECT json_agg(row_to_json(homeSearchNote)) from homeSearchNote where homeSearchNote.homesearch_id=homeSearch.id) notes  FROM homeSearch  WHERE client->>'user_id'=100  LIMIT 5;  

However, this returns:

ERROR:  column "client" does not exist  LINE 19: WHERE client->>user_id=100  

If I run the query without the WHERE clause in PGAdmin, I can clearly see a table with a 'client' column of type JSON.

Can anyone comment what would be the right way to place / write the WHERE clause ?

Much appreciated!

https://stackoverflow.com/questions/66809065/trying-to-filter-a-sql-query-on-a-json-field March 26, 2021 at 07:41AM