Thread: BUG #8293: There are no methods to convert json scalar text to text in v9.3 beta2

The following bug has been logged on the website:

Bug reference:      8293
Logged by:          Yaroslav Potapov
Email address:      jaroslav.potapov@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   All
Description:

SELECT '"a\"b"'::json::text


returns text: '"a\"b"' ,
but it must return 'a"b' in my opinion.


Thank you.

On Wed, Jul 10, 2013 at 07:07:54PM +0000, jaroslav.potapov@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      8293
> Logged by:          Yaroslav Potapov
> Email address:      jaroslav.potapov@gmail.com
> PostgreSQL version: Unsupported/Unknown
> Operating system:   All
> Description:
>
> SELECT '"a\"b"'::json::text
>
>
> returns text: '"a\"b"' ,
> but it must return 'a"b' in my opinion.

I see you didn't get a reply, so let me try.  I am no JSON expert, but I
think what is happening is that the system stores "a\"b" because that is
what a JSON/Javascript interpreter would need to understand that value.
It would convert "a\"b" to a"b.  If we just stored a"b, the interpreter
would throw an error on input.

You can see this a little bit using 9.3 beta to pull values based on
keys:

    SELECT json_extract_path('{"\"a": "b\"c"}'::json, '"a');
     json_extract_path
    -------------------
     "b\"c"
    (1 row)

    SELECT json_extract_path('{"\"a": "b\"c"}'::json, '\"a');
     json_extract_path
    -------------------

    (1 row)

Notice the key is "a, not \"a.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On 08/02/2013 01:04 PM, Bruce Momjian wrote:
> On Wed, Jul 10, 2013 at 07:07:54PM +0000, jaroslav.potapov@gmail.com wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      8293
>> Logged by:          Yaroslav Potapov
>> Email address:      jaroslav.potapov@gmail.com
>> PostgreSQL version: Unsupported/Unknown
>> Operating system:   All
>> Description:
>>
>> SELECT '"a\"b"'::json::text
>>
>>
>> returns text: '"a\"b"' ,
>> but it must return 'a"b' in my opinion.
> I see you didn't get a reply, so let me try.  I am no JSON expert, but I
> think what is happening is that the system stores "a\"b" because that is
> what a JSON/Javascript interpreter would need to understand that value.
> It would convert "a\"b" to a"b.  If we just stored a"b, the interpreter
> would throw an error on input.


Well, yes, although the shorter answer is simply that we would not be
storing legal JSON, which is defined by a standard, not by the
requirements of interpreters.


There is no specific cast to text for json. The cast therefore calls the
type's output function, which of course delivers the json string. To do
as the OP suggests would require us to treat JSON scalar strings as
special, since we would certainly not want to de-escape any JSON that
wasn't just a scalar string. e.g. removing quotes or backslashes in this
would be a major error:

    select '{"\"a": "b\"c"}'::json::text;

IOW, this isn't a bug in my view.

What we should possibly provide is a function to de-escape JSON scalar
strings explicitly. It would be a simple extension to write,
particularly for 9.3 where the JSON parser is hookable. (Or it could
easily be added as a core function of course).

cheers

andrew