Re: Unpacking scalar JSON values

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;