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

Query a view via nested subqueries – Postgresql

Hi have a v_message view that looks like this

SELECT 
message.id AS "id",
body AS "body",
message.created_at AS "createdAt",
from_phone_number AS "fromPhoneNumber",
to_phone_number AS "toPhoneNumber",
is_read AS "isRead",
json_build_object(
  'id', agent.id,
  'firstName', agent.first_name,
  'lastName', agent.last_name,
  'avatarLink', agent.avatar_link
) AS "createdByAgent",
json_build_object(
  'id', client.id,
  'firstName', client.first_name,
  'displayName', client.display_name
) AS "createdByClient"
FROM message 
LEFT JOIN agent
ON message.created_by_agent_id = agent.id
LEFt JOIN client
ON message.created_by_client_id = client.id

I now would like to apply a WHERE query like so

SELECT * FROM v_message WHERE  v_message."createdByClient".id = 1

However I’m presented with the following error:

ERROR:  missing FROM-clause entry for table "createdByClient"
Tags:

2

Answers


  1. 0

    Elements of a JSON object aren’t accessed with dot notation. Instead of v_message."createdByClient".id = 1, the proper syntax is (v_message."createdByClient" -> 'id')::integer = 1.

    Login or Signup to reply.
  2. 0

    The operator ->> must be used to obtain the element id as text/varchar.

    Since the returned result is text, we must use ::integer to convert it to an integer before comparing it to an integer:

    SELECT * 
FROM v_message 
WHERE  ("createdByClient" ->> 'id')::integer = 1

    OR convert 1 to varchar :

    SELECT * 
FROM v_message 
WHERE  "createdByClient" ->> 'id' = '1'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search