On 2025-11-13 at 11:49:48 UTC-0500 (Thu, 13 Nov 2025 16:49:48 +0000)
Smith via Postfix-users <[email protected]>
is rumored to have said:
(I am on PG18 incase it makes any difference to answers proposed)
Maybe asking on a relevant mailing list would work better?
s/fix/gres/ perhaps?
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]
--
Bill Cole
[email protected] or [email protected]
(AKA @[email protected] and many *@billmail.scconsult.com
addresses)
Not Currently Available For Hire
_______________________________________________
Postfix-users mailing list -- [email protected]
To unsubscribe send an email to [email protected]
