how do I convert text to jsonB

Question

What is the proper way to convert any text (or varchar) to jsonB type in Postgres (version 9.6) ?

For example, here I am using two methods and I am getting different results:

Method 1:

dev=# select '[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::jsonb;
                                            jsonb                                             
----------------------------------------------------------------------------------------------
 [{"field": 15, "value": "1", "operator": 0}, {"field": 15, "value": "2", "operator": 0}, 55]
(1 row)

Method 2 , which doesn't produce the desired results, btw:

dev=# select to_jsonb('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::text);
                                              to_jsonb                                              
----------------------------------------------------------------------------------------------------
 "[{\"field\":15,\"operator\":0,\"value\":\"1\"},{\"field\":15,\"operator\":0,\"value\":\"2\"},55]"
(1 row)

dev=#

Here, it was converted to a string, not an array. Why doesn't the second method creates an array ?

About **similar title but different problem**, *"convert sql-text to jsonb-string"* (the atomic datatype conversion), see https://stackoverflow.com/q/64974466/287948 — Peter Krauss, Nov 24 '20 at 13:53

McNets · Answer 1 · 2017-01-29T20:38:36.403

According to Postgres documentation:

to_jsonb(anyelemnt)

Returns the value as json or jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json or jsonb value.

IMHO you are providing a JSON formatted string, then you should use the first method.

to_json('Fred said "Hi."'::text)  --> "Fred said \"Hi.\""

If you try to get an array of element using to_json(text) you'll get the next error:

select *
from jsonb_array_elements_text(to_jsonb('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::text));

cannot extract elements from a scalar

But if you previously cast it to json:

select *
from jsonb_array_elements_text(to_jsonb('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]'::json));

+--------------------------------------------+
|                    value                   |
+--------------------------------------------+
| {"field": 15, "value": "1", "operator": 0} |
+--------------------------------------------+
| {"field": 15, "value": "2", "operator": 0} |
+--------------------------------------------+
| 55                                         |
+--------------------------------------------+

Well, you are talking about `json` , not `jsonb`. Also, with your example (Fred said Hi) I get incorrect results. And I don't understand the "if the data type is not built in" part, I am specifying `::text` and `text` is built in. Also which of two *conversion*s we are talking about? The one that converts my input to `::text`, or the one that parses this `::text` and creates a `json` object. Postgres documentation is not well done, If it would be clear enough I wouldn't asking on SO. — Nulik, Jan 29 '17 at 19:46
Yes, I'm sorry, I've looked at 9.3 version docs, but in fact the result is the same. — McNets, Jan 29 '17 at 20:05
I can not cast it to array because I don't know what that `json` string will be containing. What if it is going to have an associative array? Under 9.6 version, my query produces different results.That's why I am asking, because I thought, that using `to_jsonb()` function I would receive a correctly parsed `jsonb` object, however this is not the case. — Nulik, Jan 29 '17 at 21:40
I've found cast works for me. Example. select cast('[{"field":15,"operator":0,"value":"1"},{"field":15,"operator":0,"value":"2"},55]' as jsonb); — Keith John Hutchison, Nov 14 '17 at 06:02

score 17 · Answer 2 · answered Sep 06 '18 at 03:14

17

If your text is just a json format text, you could just explicitly cast it to json/jsonb like this:

select '{"a":"b"}'::jsonb

answered Sep 06 '18 at 03:14

KerlW

331
2
6

Peter Krauss · Answer 3 · 2020-11-24T15:05:03.900

Atomic type conversion and CSV-to-JSONb

A typical parse problem in open data applications is to parse line by line a CSV (or CSV-like) text into JSONB correct (atomic) datatypes. Datatypes can be defined in SQL jargon ('int', 'text', 'float', etc.) or JSON jargon ('string', 'number'):

CREATE FUNCTION csv_to_jsonb(
  p_info text,           -- the CSV line
  coltypes_sql text[],   -- the datatype list
  rgx_sep text DEFAULT '\|'  -- CSV separator, by regular expression
) RETURNS JSONb AS $f$
  SELECT to_jsonb(a) FROM (
      SELECT array_agg(CASE
          WHEN tp IN ('int','integer','smallint','bigint') THEN to_jsonb(p::bigint)
          WHEN tp IN ('number','numeric','float','double') THEN  to_jsonb(p::numeric)
          WHEN tp='boolean' THEN to_jsonb(p::boolean)
          WHEN tp IN ('json','jsonb','object','array') THEN p::jsonb
          ELSE to_jsonb(p)
        END) a
      FROM regexp_split_to_table(p_info,rgx_sep) WITH ORDINALITY t1(p,i)
      INNER JOIN unnest(coltypes_sql) WITH ORDINALITY t2(tp,j)
      ON i=j
  ) t
$f$ language SQL immutable;

-- Example:
SELECT csv_to_jsonb(
  '123|foo bar|1.2|true|99999|{"x":123,"y":"foo"}',
  array['int','text','float','boolean','bigint','object']
);
-- results  [123,   "foo bar", 1.2,    true, 99999,  {"x": 123, "y": "foo"}]
-- that is: number, string,   number,  true, number, object

how do I convert text to jsonB

3 Answers3

Atomic type conversion and CSV-to-JSONb

Linked