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

Jubbulpore

Requested functionality is one from interesting features 9.3. I read some articles where was described using pl/js8 for this purpose postgresonline.com/journal/archives/… – Prediction

Thanks. I also discovered I can do the first one by just casting it to JSON: settings::json. But it's not query-able though. Settled with regexp. – Jubbulpore

FYI there is a backport of 9.3 JSON function enhancements for PostgreSQL 9.2 adpgtech.blogspot.co.nz/2013/04/… – Amidst

Answer 1

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.

Smoker

I know this is quiet old, but: How can I search for a special field in the text, lets say WHERE settings::json = 'team_id':team_id (so to say to search for a specific field in the text of settings)? – Erie

@Erie settings::json ->> 'team_id' = team_id – Stoneware

Answer 2

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

Flophouse

I needed the exact same thing. This did the job nicely. That said, I can't speak for how performant it is though as I'm querying a relatively small dataset. – Datestamp

I wish this was stated in the doc at a very first beginning. Or if I had overread that I wish it was more obtrusive. It tok far to long to have a varchar in the db converted into a json. thx for the post. – Seeger

I recall that this used to work but recently Im getting invalid input syntax returned and can't seem to find useful docs for how to write 'valid' Stringified JSON to a varchar field – Gambia

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

Revue

Incredible. This is exactly what I needed. I'm very proud and humbled to be your first upvote on this answer. :-) Keep doing your good work, sir. – Vulgate

Thank you Chirs for kind words! You made my day :). – Revue

Agreed!!! You saved me $$$ by the post!! Thank you! – Thracophrygian

I had some text saved using escaping and this way worked as well. Thanks, sir! You saved the day – Luxuriance

Answer 4

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

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

Calvillo

Does this require a table rewrite? – Roscoeroscommon

Not sure if this solves the problem posed, changing the type will require a refactor of the table – Enriquetaenriquez

damn! this works perfectly! – Gaily

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

Suppuration

Here's mine as a one liner: update users set settings = regexp_replace(trim(both '"' from settings), '\\"', '"', 'g')::json; – Daguerreotype

This will fail if the json being converted has another json as string in it. The safer way is to extract the string as text and convert it to json: `select settings #>>'{}'::jsonb from users;. See my answer below for longer description. – Revue

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

Em

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;

Galah