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 ?

Answer 1

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

Answer 2

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

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