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 
(1 row)

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

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

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

test=# select json_typeof((('"{\"foo\":\"bar\"}"'::json) #>>'{}')::json);
(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:

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: