Alternative ways to extract the contents of a JSON string

Consider the following query:

select '"{\"foo\":\"bar\"}"'::json;

This will return a single record of a single element containing a JSON string. See:

test=# select json_typeof('"{\"foo\":\"bar\"}"'::json);                                                                                                                                                                                                                                                                                                                                                                                   json_typeof 
-------------
 string
(1 row)

It is possible to extract the contents of the string as follows:

=# select ('"{\"foo\":\"bar\"}"'::json) #>>'{}';
     json      
---------------
 {"foo":"bar"}
(1 row)

From this point onward, the result can be cast as a JSON object:

test=# select json_typeof((('"{\"foo\":\"bar\"}"'::json) #>>'{}')::json);
 json_typeof 
-------------
 object
(1 row)

This way seems magical. I define no path within the extraction operator, yet what is returned is not what I passed. I worry that I will confuse the next maintainer to look at this logic. Is there a less magical way to do this?



Read more here: https://stackoverflow.com/questions/68092331/alternative-ways-to-extract-the-contents-of-a-json-string

Content Attribution

This content was originally published by Ed Carrel at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: