PostgreSQL 9.2 - Convert TEXT json string to type json/hstore

Question

I have a TEXT column containing valid JSON string.

CREATE TABLE users(settings TEXT);

INSERT INTO users VALUES ('{"language":"en","gender":"male"}');
INSERT INTO users VALUES ('{"language":"fr","gender":"female"}');
INSERT INTO users VALUES ('{"language":"es","gender":"female"}');
INSERT INTO users VALUES ('{"language":"en","gender":"male"}');

I want to transform some fields into a query-able format.

A REGEXP_REPLACE for each field would do (language field and gender field). But since it's valid JSON, is there way to:

• Convert into JSON type
• Convert into hstore type
• Or any other feasible ways

SQLFiddle: http://sqlfiddle.com/#!12/54823

Answer 1

SELECT cast(settings AS json) from users;

EDIT 7 years later

I highly suggest that you don't use unstructured columns unless your data is unstructured. RDBMS go a very long way. We built a fairly large platform and used user settings as a json column, and it endedup becoming a junk drawer which needed to be cleaned up many years later

Answer 2

Or in a shortest way than Reza:

SELECT settings::json FROM users;

Then, for selecting language for instance:

SELECT settings::json->>'language' FROM users;

More details on the official documentation.

Answer 3

The ::jsonb won't work if your column is json already and contains string at it root level. Here is one liner that convert such string to JSON:

SELECT (settings #>> '{}')::jsonb -> 'language' from users;

I've found this answer in here

The statement first extract the root level string as text through #>> operator that was given an empty path. Note that simply casting such string to text (::text) won't work as it will escape all quotes. Next such extracted string is parsed to json object (::jsonb).

An alternative version of this query is to put the json string in to an array and then extract it first element as text:

select cast(json_build_array(settings)->>0 as json)

To fix the issue you can as well convert all your fields with string at root level to json with the following command:

UPDATE users
SET
    settings = settings #>>'{}'::jsonb
WHERE settings ->> 'language' is  NULL

Answer 4

Here is a solution from Postgresql: Converting TEXT columns to JSON:

ALTER TABLE table1 ALTER COLUMN col1 TYPE JSON USING col1::JSON;

Answer 5

So I had an issue where the text was JSON. If you have this issue use this query instead. Where COLUMN is the column that contains the JSONB or JSON datatype and ATTRIBUTE is the attribute of the JSON that is a string, that you want converted into JSON.

The text will look like this, "{\"junk5\": 283774663, \"junk2\": 0, \"junk1\": 1218478497, \"junk3\":1923, \"junk4\": 63278342}"

SELECT CAST(TRIM(both '"' from jsonstring) as JSON)
FROM (
    SELECT REPLACE(cast(COLUMN->'ATTRIBUTE' as text), '\"', '"')
    as jsonString from TABLE where cast(COLUMN->'ATTRIBUTE' as text)LIKE '%\\%'
) as JSON_CONVERTING

Answer 6

If you need an index on it, create an immutable function that takes the json as input and yields the field you want as output in a pl language, e.g.:

create function extract_language(text) returns text as $$
  -- parse $1 as json
  -- return $1.language
$$ language whatever immutable;

Then add an index on the expression:

create index users_language on users(extract_language(settings));

The index will then (potentially) get used in queries such as:

select * from users where extract_language(settings) = 'en';

Answer 7

Adding to another comment, here is a one-liner in a query(without the need to update)

regexp_replace(trim(both '"' from settings::text), '\\"', '"', 'g')::json as column_name;