Re: Unpacking scalar JSON values

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Ü