query json in postgresql with "\" inside-CodePudding

I have a json which looks like this:

"{\"chat:title\":\"Random name Comunidad\",\"chat:type\":\"supergroup\",\"command:start:count\":4,\"command:start:ts\":1648146227630,\"command:help:count\":1,\"command:help:ts\":1648145742922,\"command:price:count\":3,\"command:price:ts\":1648146698585}"

And I'd like to query it and get the name out of it. I tried classically:

 SELECT metadata->>'chat'
 SELECT metadata->>'chat:title'

but it's not working. I think it's because of the backslash in the string... Any ideas how to query it?

CodePudding user response：

Just remove backslash from the string:

with t as (
select '{\"chat:title\":\"Random name Comunidad\",\"chat:type\":\"supergroup\",\"command:start:count\":4,\"command:start:ts\":1648146227630,\"command:help:count\":1,\"command:help:ts\":1648145742922,\"command:price:count\":3,\"command:price:ts\":1648146698585}'
as metadata)
select replace(t.metadata, '\','')::json ->> 'chat:title'
from t

CodePudding user response：

Is it of JSON type and the content is as is you showed? If so then:

select btrim(replace(metadata::text,'\',''),'"')::json->>'chat:title';

CodePudding user response：

Manually removing the backslashes might work, but will eventually fail on some inputs and just make things worse.

The right way is to use the existing JSON operators to fix it (unless of course someone has already made it worse).

SELECT (metadata->>0)::jsonb ->> 'chat:title'

But really you should fix the source that is causing the problem, and update the existing data to store the fix.

update whatever set metadata=(metadata->>0)::jsonb;

If only some of the data has been crappified, you might need to where clause to distinguish the good from the bad and so only fix the bad.