Re: BUG #11021: How to extract text value from json scalar?

On Tue, Jul 22, 2014 at 8:02 PM, <kolmyk(at)hotmail(dot)com> wrote:
> I'm using json_each function for iterate keys and values in my function, but
> string values contains quotes.
> I can use json_each_text, but sometimes I need to json value.
> Can I extract text value from json scalar?
Not that I am aware of. You could always compare the results of
json_each and json_each_text and grab the fields that have as only
differences those quotes, but this would be more costly than the trick
you are mentioning below. Also, a text without quotes is not valid in
json AFAIK, that's why you cannot cast a value obtained with
json_each_text back to json. If I'm wrong, anyone, feel free to
correct though.

With your solution, nested json fields are however in correct shape:
=# SELECT key, value, ('[' || value ||']')::json->>0 as col3
FROM json_each('{"a":123,"b":"first","c":{"d":1,"e":"text"}}');
key | value | col3
-----+--------------------+--------------------
a | 123 | 123
b | "first" | first
c | {"d":1,"e":"text"} | {"d":1,"e":"text"}
(3 rows)
You could always do some extra operations on the fields obtained, or
catch casting errors if your function is for example in plpgsql.

> Now I'm using this alternative script:
> ('[' || value ||']')::json->>0
>
> Example:
> select key, value, ('[' || value ||']')::json->>0 from
> json_each('{"a":123,"b":"first"}');
By the way, this is not a bug, and the mailing list pgsql-general is
more appropriate.
Regards,
--
Michael