Обсуждение: 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@2ndquadrant.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@fdr.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@fdr.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