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

On Tue, Jul 22, 2014 at 8:02 PM, <kolmyk@hotmail.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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs