CODEWITHSUNDEEP
jsonpostgresqljsonb
Fabíola Magossi
Fabíola Magossi

Reputation: 39

Jsonb extract in PostgresSQL - problem with '{}'::

I have this jsonb column in a PostgresSQL table.

{
"{\"start\":\"14:00\",\"end\":\"14:50\"}",
"{\"start\":\"14:51\",\"end\":\"15:40\"}",
"{\"start\":\"15:41\",\"end\":\"16:30\"}",
"{\"start\":\"16:31\",\"end\":\"17:20\"}"
}

I need to extract all values of start and end. I want the result to be like this

id | start1 | end1 | start2 | end2 | start3 | end3 | start4 | end4

or

id | start1 | end1 
id | start2 | end2 
id | start3 | end3 
id | start4 | end4

The usual ->> doesn't work for this and I have no clue how can I do that.

Upvotes: 0

Views: 194

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19694

You don't say what version of Postgres you are using, but if it probably has:

https://www.postgresql.org/docs/12/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION

standard_conforming_strings (boolean)

This controls whether ordinary string literals ('...') treat backslashes The presence of this parameter can also be taken as an indication that the escape string syntax (E'...') is supported. Escape string syntax (Section 4.1.2.2) should be used if an application desires backslashes to be treated as escape characters.

In that case to deal with the escapes in your JSON you need to do:

select E'{\"start\":\"14:00\",\"end\":\"14:50\"}'::jsonb;
               jsonb                
------------------------------------
 {"end": "14:50", "start": "14:00"}
(1 row)

select E'{\"start\":\"14:00\",\"end\":\"14:50\"}'::jsonb ->> 'start';
 ?column? 
----------
 14:00


select E'[
{\"start\":\"14:00\",\"end\":\"14:50\"},
{\"start\":\"14:51\",\"end\":\"15:40\"},
{\"start\":\"15:41\",\"end\":\"16:30\"},
{\"start\":\"16:31\",\"end\":\"17:20\"}
]'::jsonb;

--------------------------------------------------------------------------------------------------------------------------------------------------
 [{"end": "14:50", "start": "14:00"}, {"end": "15:40", "start": "14:51"}, {"end": "16:30", "start": "15:41"}, {"end": "17:20", "start": "16:31"}]

Upvotes: 1

Related Questions