JSON value may consist of a string value. eg.:
postgres=# SELECT to_json('Some "text"'::TEXT);
to_json
-----------------
"Some \"text\""
How can I extract that string as a Postgres text value?
::TEXT doesn't work. It returns quoted json, not the original string:
postgres=# SELECT to_json('Some "text"'::TEXT)::TEXT;
to_json
-----------------
"Some \"text\""
P.S. I'm using PostgreSQL 9.3
In 9.4.4 using the #>> operator works for me:
select to_json('test'::text) #>> '{}';
To use with a table column:
select jsoncol #>> '{}' from mytable;
to_json(...) is simply an easy way to create a JSON value to work with as an example in a short one line statement. Certainly you'd replace it with the name of a JSON column if you were querying a table as you describe. Also, to clear up a point of potential confusion, your cast (...)::text is redundant as the #>> operator returns text by definition (and is the reason for using the operator in the first place). You could keep the parentheses but drop the cast ::text.
#>> and '{}' are doing? I can't quite follow this and neither term is google friendly. This answer did fix my problem, I just want to know why.#>> operator is here.text. It might look like a string but it is a JSON object. To convert that object from JSON to text, use the #>> operator. But that operator needs you to specify a path. The path to that root object is {}. So SELECT '"test"'::jsonb #>> '{}' means "get the object at the root path and convert it to text".There is no way in PostgreSQL to deconstruct a scalar JSON object. Thus, as you point out,
select length(to_json('Some "text"'::TEXT) ::TEXT);
is 15,
The trick is to convert the JSON into an array of one JSON element, then extract that element using ->>.
select length( array_to_json(array[to_json('Some "text"'::TEXT)])->>0 );
will return 11.
json_extract_path_text() cannot reference the root element (AFAIK).
from_json got proposed, but not implemented wiki.postgresql.org/wiki/JSON_API_Brainstorm
Mr. Curious was curious about this as well. In addition to the #>> '{}' operator, in 9.6+ one can get the value of a jsonb string with the ->> operator:
select to_jsonb('Some "text"'::TEXT)->>0;
?column?
-------------
Some "text"
(1 row)
If one has a json value, then the solution is to cast into jsonb first:
select to_json('Some "text"'::TEXT)::jsonb->>0;
?column?
-------------
Some "text"
(1 row)
->> works for me.
postgres version:
<postgres.version>11.6</postgres.version>
Query:
select object_details->'valuationDate' as asofJson, object_details->>'valuationDate' as asofText from MyJsonbTable;
Output:
asofJson asofText
"2020-06-26" 2020-06-26
"2020-06-25" 2020-06-25
"2020-06-25" 2020-06-25
"2020-06-25" 2020-06-25
-> and ->> when using a key. See this answer or this answer.Postgres 17 did introduce the dedicated query function json_value for this purpose.
json_value(my_column, '$')
which is short for
json_value(my_column, '$' RETURNING text)
In theory you can pass any JSON path expression, but here we use $ to extract the top-level value.
Notice this will throw an error if my_column is a non-scalar JSON value. You can use
json_value(my_column, '$' RETURNING text NULL ON ERROR)
to avoid the exception. There is no check that the JSON value is actually a string, other scalars will also be converted to text. A JSON null will be converted to a NULL text value.
I've faced this challenge as well. If you have something that is a 'mulitselect' json you can use
select jsonb_array_elements_text(yourjsoncolumn) from yourtable
to convert the list to individual text elements. If I do the other suggestions above I still get the quoted json value.
yourjsoncolumn ->> 0 would certainly work for you, but return only the first value not every element of the array. jsonb_array_elements(yourjsoncolumn) #>> '{}' would also work, but of course be uglier - if you have a JSON array, jsonb_array_elements_text is definitely the way to goAn easy way of doing this:
SELECT ('[' || to_json('Some "text"'::TEXT) || ']')::json ->> 0;
Just convert the json string into a json list
