Alternative ways to extract the contents of a JSON string

Question

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. This seems like passing no index to an array accessor, and getting an element back.

I worry that I will confuse the next maintainer to look at this logic.

Is there a less magical way to do this?

I don't know what you're asking. Is it that you have a column of type json and you want it rendered as text, eg `{"foo":"bar"}`? — Bohemian, Jun 23 '21 at 01:09
@Bohemian I was trying to simplify this down as much as I could. The practical use-case here is that I have a `json` column that mistakenly contains json _strings_ which contain quoted json objects, rather than _just_ json objects. My example above is the same: a json object encapsulated by a json string. I can extract using the above operator, but I could see it being really confusing. This is less of a "I'm stuck" question, and more of a "help me find a better way" question. — Ed Carrel, Jun 23 '21 at 01:17
Also, this might be a question for the postgres mailing list. I can float it there. — Ed Carrel, Jun 23 '21 at 01:20
Why don't you store `'{"foo":"bar"}'` directly storing a JSON key/value pair as an escaped JSON string doesn't really make sense. — , Jun 23 '21 at 05:15

Erwin Brandstetter · Answer 1 · 2021-07-02T12:00:01.337

But you did define a path. Defining "root" as path is just another path. And that's just what the #>> operator is for:

Extracts JSON sub-object at the specified path as text.

Rendering as text effectively applies the escape characters in the string. When casting back to json the special meaning of double-quotes (not escaped any more) kicks in. Nothing magic there. No better way to do it.

If you expect it to be confusing to the afterlife, add comments explaining what you are doing there.

Maybe, in the spirit of clarity, you might use the equivalent function json_extract_path_text() instead. The manual:

Extracts JSON sub-object at the specified path as text. (This is functionally equivalent to the #>> operator.)

Now, the function has a VARIADIC parameter, and you typically enter path elements one-by-one, like the example in the manual demonstrates:

json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}',
'f4', 'f6') → foo

You cannot enter the "root" path this way. But (what the manual does not add at this point) you can alternatively provide an actual array after adding the keyword VARIADIC. See:

Pass multiple values in single parameter

So this does the trick:

SELECT json_extract_path_text('"{\"foo\":\"bar\"}"'::json, VARIADIC '{}')::json;

And since we are all about being explicit, use the verbose SQL standard cast syntax:

SELECT cast(json_extract_path_text('"{\"foo\":\"bar\"}"'::json, VARIADIC '{}') AS json)

Any clearer, yet? (I would personally prefer your shorter original, but I may be biased, being a "native speaker" of Postgres..)

The question is, why do you have that odd JSON literal including escapes as JSON string in the first place?

"(The question is, why do you have that odd JSON literal including escapes as JSON string in the first place?)" It was data mistakenly written as text from the application code. :grimacing: The query I was writing was to clean up the data. — Ed Carrel, Jul 02 '21 at 06:15
@EdCarrel: I see. The remaining question then: Is this question answered properly? — Erwin Brandstetter, Jul 03 '21 at 02:54

Alternative ways to extract the contents of a JSON string

1 Answers1

Linked