1

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?

klin
  • 112,967
  • 15
  • 204
  • 232
zcaudate
  • 13,998
  • 7
  • 64
  • 124
  • 1
    To check the type, here a useful answer: https://stackoverflow.com/questions/31732471/checking-the-type-of-a-json-property. And to re-encode it: https://stackoverflow.com/questions/41924784/how-do-i-convert-text-to-jsonb – Iván Aug 14 '22 at 23:47
  • 1
    thanks. those links were really good. – zcaudate Aug 15 '22 at 00:45

1 Answers1

3

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)
klin
  • 112,967
  • 15
  • 204
  • 232