How to convert a json string to text?

Question

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

http://stackoverflow.com/q/19414361/562459 might help. Might not. — Mike Sherrill 'Cat Recall', Nov 30 '14 at 16:30
Similar problem with array of strings, https://stackoverflow.com/q/45243186/287948 — Peter Krauss, Jul 22 '17 at 00:51
Related: [How to convert Postgres json(b) to integer?](https://stackoverflow.com/q/20236421/1048572), [to float?](https://stackoverflow.com/q/24826385/1048572), [to boolean?](https://stackoverflow.com/q/33041617/1048572) — Bergi, Jul 07 '22 at 00:26

Ian Timothy · Answer 1 · 2018-08-30T11:12:26.157

226

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;

edited Aug 30 '18 at 11:12

answered Aug 01 '15 at 01:09

Ian Timothy

2,623
3
15
10

3

Seems to be the simplest solution in Postgres 9.4. However doesn't work for 9.3. – e79ene Aug 14 '15 at 11:19
This is the correct answer. Although the `to_json` bit is a bit confusing. Assuming you have a column of type jsonb that stores some text, this worked for me: `select (value#>>'{}')::text as value_text from ...` – hasen Nov 02 '15 at 17:18
3

@hasen The OP states he is trying to extract text from a JSON value and the `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`. – Ian Timothy Nov 27 '15 at 09:52
Without the potential confusion of the `to_json()` call: `SELECT '"test"'::jsonb #>>'{}';`. The `#>>'{}'` is the important (if unintuitive) part. – theory Feb 19 '16 at 02:38
1

Could someone spell out what `#>>` 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. – valadil Oct 11 '16 at 15:48
2

@valadil The documentation for the `#>>` operator is [here](https://www.postgresql.org/docs/9.6/static/functions-json.html). – Ian Timothy Oct 12 '16 at 07:04
4

@valadil In this case, there is a top level, or root, JSON object `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". – Ian Timothy Oct 12 '16 at 07:10
All of these `->>` and `#>> '{}'` answers go "blooey" if the value might be `NULL`. Is there a way to get the result `null` as a string when the value is `NULL`, ? (I mean without a `CASE` statement that specifically checks for `NULL`.) E.g. in Javascript `JSON.stringify(null)` is `'null'`. – user9645 Apr 15 '22 at 14:44
@user9645 without including exactly what you've tried and the result, "blooey" doesn't mean anything because, for example, `select to_json(null::text) #>> '{}' is null` returns true as expected and does not "go blooey". But you'll want to post a new question about that because it's not in the scope of this question. – Ian Timothy Apr 15 '22 at 15:30
@IanTimothy - Sorry, I should not have said "blooey" what I mean is that when the input is `NULL` the output ought to be `"null"` (a string), not `NULL`. I don't see any way to make that happen without a CASE statement that checks for `NULL`. – user9645 Apr 28 '22 at 17:29

score 78 · Accepted Answer · edited Jun 29 '18 at 18:31

78

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.

edited Jun 29 '18 at 18:31

Soviut

88,194
49
192
260

answered Nov 30 '14 at 17:25

Robert M. Lefkowitz

1,437
11
4

9

It's a pity that `json_extract_path_text()` cannot reference the root element (AFAIK). – Erwin Brandstetter Dec 01 '14 at 03:50
3

interestingly, there was a brainstorming discussion apparently back at the API designing stage in 2012 in which a function `from_json` got proposed, but not implemented https://wiki.postgresql.org/wiki/JSON_API_Brainstorm – nikola Feb 19 '15 at 17:41
3

This is outdated. See other answers for a simpler solution with current versions of postgre. – yankee May 30 '21 at 18:50

score 27 · Answer 3 · answered Nov 07 '19 at 19:21

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)

curious_soul · Answer 4 · 2020-06-29T07:19:21.713

8

->> 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

edited Jun 29 '20 at 07:19

answered Jun 29 '20 at 05:40

curious_soul

559
1
8
29

Thanks for pointing out, i corrected the version above – curious_soul Jun 29 '20 at 07:19
3

Original question is how to get a JSON string's value as text with (no object key). This answer is just the difference between `->` and `->>` when using a key. See [this answer](https://stackoverflow.com/a/31757242/912717) or [this answer](https://stackoverflow.com/a/58755595/912717). – Ian Timothy Oct 05 '20 at 20:13
This one is correct! – Anggi Permana Harianja Feb 02 '23 at 16:38

score -2 · Answer 5 · edited Jun 29 '18 at 18:31

-2

An easy way of doing this:

SELECT  ('[' || to_json('Some "text"'::TEXT) || ']')::json ->> 0;

Just convert the json string into a json list

edited Jun 29 '18 at 18:31

Soviut

88,194
49
192
260

answered Aug 24 '15 at 18:07

Zhemin Zhou

5
1

How to convert a json string to text?

5 Answers5

Linked

Related