(I am on PG18 incase it makes any difference to answers proposed)
A script dumped a whole load of quoted json into a jsonb column which means
queries are not working as expected because the column contents is not true
json.
The good news is it is only one level of nesting, because the source is an AWS
SQS post from AWS SNS about another AWS service. So what happed was the
script just dumped the SQS post body into the insert without unescaping first.
So it looks something like this :
{"Type": "Notification", "Message":
"{\"eventType\":\"Delivery\",\"mail\":{\"timestamp\":
The "Notification" is from AWS SNS, and the "Message" is from the other AWS
service.
I spent a little time with Mr Google and thought I had an answer being a
generated column, but that's not working for me :
alter table sqs_data add column sqs_msg_inner jsonb GENERATED ALWAYS AS (
(sqs_msg #>> '{Message}')::jsonb ) STORED;
ERROR: invalid input syntax for type jsonb
DETAIL: Token "Successfully" is invalid.
CONTEXT: JSON data, line 1: Successfully...
So I tried:
select sqs_msg from sqs_data where sqs_msg::text like '%Successfully%';
Which returns two rows, which are valid json, but are not quoted. Could that be
affecting the generated column attempt ?
The content displayed for those two looks like:
{"Type": "Notification", "Message": "Successfully validated....
I'm a bit over my head on this one. As is likely clear, I am still a bit of a
postgres json noob.
_______________________________________________
Postfix-users mailing list -- [email protected]
To unsubscribe send an email to [email protected]
