json format cast to varchar

Question

I write the query in postgresql I have store the data in json format of one column and now I need the particular value of the json

message_data :{"apiMessageId":"162fbbbe8f0e2a67","userEmail":"[email protected]","cloudId":1}

This is data and I want to only userEmail and this is my query

SELECT id, process_id, process_state, cast(message_data->> \'userEmail\' AS VARCHAR) AS userEmail, inserted_at, completed_at 
FROM realtimeincomingemailnotificationsqueue 
where api_message_id = '162fbbbe8f0e2a67';

cast(message_data->> 'userEmail')::varchar, but it is text anyway, so I dont see any need in additional cast — Vao Tsun
– Vao Tsun, Commented May 22, 2018 at 13:33
The backslash does not escape anything in SQL. You should use 'userEmail' rather than \'userEmail\' — user330315
– user330315, Commented May 22, 2018 at 13:37

Vao Tsun · Accepted Answer · 2018-05-22 13:37:14Z

1

https://www.postgresql.org/docs/current/static/functions-json.html

->> text Get JSON object field as text

(emphasis mine)

so I don't why you would want to cast text to varchar, but if you really do, use

cast(message_data->> 'userEmail')::varchar

answered May 22, 2018 at 13:37

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

json format cast to varchar

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related