Re: Unpacking scalar JSON values

Per report of Armin Ronacher, it's not clear how to take a scalar JSON
string and unquote it into a regular Postgres "text" value, given what
I can see here:
http://www.postgresql.org/docs/9.3/static/functions-json.html

Example:

SELECT '"a json string"'::json;

(Although this some problem could play out with other scalar JSON types):

SELECT '4'::json;
SELECT '2.0'::json;

This use cases arises from some of the extant unpacking operations,
such as json_array_elements. It's not that strange to have a value
something something like this in a JSON:

'{"tags": ["a \" string", "b", "c"]}'

Thoughts?

On 08/24/2013 11:36 PM, Daniel Farina wrote:
> Per report of Armin Ronacher, it's not clear how to take a scalar JSON
> string and unquote it into a regular Postgres "text" value, given what
> I can see here:
> http://www.postgresql.org/docs/9.3/static/functions-json.html
>
> Example:
>
> SELECT '"a json string"'::json;
>
> (Although this some problem could play out with other scalar JSON types):
>
> SELECT '4'::json;
> SELECT '2.0'::json;
>
> This use cases arises from some of the extant unpacking operations,
> such as json_array_elements. It's not that strange to have a value
> something something like this in a JSON:
>
> '{"tags": ["a \" string", "b", "c"]}'
>
> Thoughts?
This was discussed to death at some point during development and
the prevailing consensus was that json "type" is not representing the
underlying structure/class instance/object but a "string which encodes
this object"

so if you convert a restricted ("must comply to JSON Spec") string to
unrestricted string you really just do a NoOp vast.

I guess this is also why the new hstore-based json (jstore ?) class
needs to be
different as it actually *stores* a structured type resulting from
parsing the
json making many things different

For example currently many other things are unintuitive if you expect
stored type to hold the structure the json evaluates to and not just the
evaluated source string:

hannu=# SELECT 'null'::json::text;
text
------
null
(1 row)

hannu=# SELECT '{"a":1, "a":null}'::json::text;
text
-------------------
{"a":1, "a":null}
(1 row)

Regards

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

On Sat, Aug 24, 2013 at 3:09 PM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
> On 08/24/2013 11:36 PM, Daniel Farina wrote:
>> Per report of Armin Ronacher, it's not clear how to take a scalar JSON
>> string and unquote it into a regular Postgres "text" value, given what
>> I can see here:
>> http://www.postgresql.org/docs/9.3/static/functions-json.html
>>
>> Example:
>>
>> SELECT '"a json string"'::json;
>>
>> (Although this some problem could play out with other scalar JSON types):
>>
>> SELECT '4'::json;
>> SELECT '2.0'::json;
>>
>> This use cases arises from some of the extant unpacking operations,
>> such as json_array_elements. It's not that strange to have a value
>> something something like this in a JSON:
>>
>> '{"tags": ["a \" string", "b", "c"]}'
>>
>> Thoughts?
> This was discussed to death at some point during development and
> the prevailing consensus was that json "type" is not representing the
> underlying structure/class instance/object but a "string which encodes
> this object"
>
> so if you convert a restricted ("must comply to JSON Spec") string to
> unrestricted string you really just do a NoOp vast.

This doesn't make a lot of sense to me.

select * from json_each_text('{"key": "va\"lue"}'); is handy and gives
one the json value of the text -- that is to say, dequoted. So it's
not like unquoting is not already an operation seen in some of the
operators:

select * from json_each_text('{"key": "va\"lue"}');
key | value
-----+--------
key | va"lue
(1 row)

But there's no good way I can find from the documentation to do it
with a scalar: select ('"va\"lue"'::json)::text;

On Sat, Aug 24, 2013 at 6:04 PM, Daniel Farina <daniel(at)fdr(dot)io> wrote:
> But there's no good way I can find from the documentation to do it
> with a scalar: select ('"va\"lue"'::json)::text;

Triggered send by accident:

select ('"va\"lue"'::json)::text;
text
-----------
"va\"lue"
(1 row)

the JSON escaping is retained. That may be reasonable for a
text-cast, so I'm not suggesting its reinterpretation, but there is no
operator I can identify immediately from the documentation to convert
a JSON string value into a Postgres one like json_each_text, except on
a json that contains a scalar JSON string.

On 08/24/2013 09:08 PM, Daniel Farina wrote:
> On Sat, Aug 24, 2013 at 6:04 PM, Daniel Farina <daniel(at)fdr(dot)io> wrote:
>> But there's no good way I can find from the documentation to do it
>> with a scalar: select ('"va\"lue"'::json)::text;
> Triggered send by accident:
>
> select ('"va\"lue"'::json)::text;
> text
> -----------
> "va\"lue"
> (1 row)
>
> the JSON escaping is retained. That may be reasonable for a
> text-cast, so I'm not suggesting its reinterpretation, but there is no
> operator I can identify immediately from the documentation to convert
> a JSON string value into a Postgres one like json_each_text, except on
> a json that contains a scalar JSON string.
>
>

This came up recently on -bugs. See
<http://www.postgresql.org/message-id/51FBF787.5000408@dunslane.net>,
where I suggested we expose the dequoting function.

cheers

andrew

>
> This came up recently on -bugs. See
> <http://www.postgresql.org/message-id/51FBF787.5000408@dunslane.net>,
> where I suggested we expose the dequoting function.

+1.

As it is, I use btrim() heaviy with JSON data.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com