CODEWITHSUNDEEP
jsonpostgresqljsonb
zcaudate
zcaudate

Reputation: 14258

How to make sure that a jsonb object is not an encoded string?

I have found sometimes a jsonb object:

{"a": 1, "b": 2}

will get re-encoded and stored as a jsonb string:

"{\"a\": 1, \"b\": 2}"

is there a way to write a function that will reparse the string when input is not a jsonb object?

Upvotes: 1

Views: 66

Answers (1)

klin
klin

Reputation: 121654

The #>> operator (Extracts JSON sub-object at the specified path as text) does the job:

select ('"{\"a\": 1, \"b\": 2}"'::jsonb #>> '{}')::jsonb

This operator behavior is not officially documented. It appears to be a side effect of its underlying function. Oddly enough, its twin operator #> doesn't work that way, though it would be even more logical. It's probably worth asking Postgres developers to solve this, preferably by adding a new decoding function. While waiting for a system solution, you can define a simple SQL function to make queries clearer in cases where the problem occurs frequently.

create or replace function jsonb_unescape(text)
returns jsonb language sql immutable as $$
    select ($1::jsonb #>> '{}')::jsonb
$$;

Note that the function works well both on escaped and plain strings:

with my_data(str) as (
values
    ('{"a": 1, "b": 2}'),
    ('"{\"a\": 1, \"b\": 2}"')
)
select str, jsonb_unescape(str)
from my_data;

          str           |  jsonb_unescape
------------------------+------------------
 {"a": 1, "b": 2}       | {"a": 1, "b": 2}
 "{\"a\": 1, \"b\": 2}" | {"a": 1, "b": 2}
(2 rows)

Upvotes: 3

Related Questions