Transforming HSTORE array values to a column in JSONB

Question

I am trying to transform my data stored in HSTORE column ('data') of Postgres.

My row values have key "entity" and value is in the array.

"entity"=>"[{'id': .............}]

I used the following code:

Alter TABLE my_table 
   ALTER COLUMN h_store_column TYPE jsonb 
   USING hstore_to_jsonb_loose(data -> 'entity');

which resulted in value as output in a new column as below:

"[{'id': .............}]"

but, with quotes "". This made it scalar in JSONB type column and doesn't let me run the query.

How can I change the value of every row in a new column named 'entity' with JSONB, without quotes?

[{'id': .............}]

SAMPLE CODE TO GENERATE SIMILAR DATA:

"key" => "[json_text_array]"

stored in hstore data type column.

When changed to JSON B type, I get {'key':'[array]'}, whereas I am after {'key': [array]} - No quotes. I tried loose functions in postgres, no help.

Please show us something reproducible/accurate. I get ERROR: function hstore_to_jsonb_loose(text) does not exist — jjanes
Hi, I just added more to description. @AkhileshMishra, here is a value of a row in hstore. "entity" => "[{'id': '11954_179415600_2441_333_1', 'vehicle': {'trip': {'tripId': '1182456', 'routeId': '2441_333', 'startDate': '20200822', 'startTime': '22:14:00', 'scheduleRelationship': 'SCHEDULED'}, 'vehicle': {'id': '11954_179415600_2441_333_1'}, 'position': {'speed': 0.0, 'bearing': 297.0, 'latitude': -33.876972, 'longitude': 151.21237}, 'timestamp': '1598100524', 'congestionLevel': 'RUNNING_SMOOTHLY', 'occupancyStatus': 'MANY_SEATS_AVAILABLE'}}]" — Nirman Kesari
{'id': …} is not valid JSON. {"id": …} would be. If your value looked like that, you could use (data -> 'entity')::jsonb. (Notice that hstore_to_jsonb_loose is absolutely unnecessary as -> 'entity' already accesses the string value in the hstore). — Bergi
Please do not put additional information in comments (especially not "code" type things). edit your question instead — a_horse_with_no_name

Akhilesh Mishra Akhilesh Mishra · Accepted Answer · 2020-08-23T17:01:49

As per your question what I understood, you have a column with type hstore having a key named entity and value as JSON ARRAY. Explanation of your problem and solution will be as:

Your Alter query mentioned in the question will through error because hstore_to_jsonb_loose function accepts hstore type value but you are passing text. So the correct statement for query should be.

    Alter TABLE my_table 
       ALTER COLUMN h_store_column TYPE jsonb 
       USING hstore_to_jsonb_loose(data) -> 'entity';

Above query will convert the hstore key-value into jsonb key value pair and update it into the column h_store_column.

So the function hstore_to_jsonb_loose will convert the data as { "entity": "[{'id':..........}]" } from which you are your extracting the JSON value of key 'entity' which is "[{'id':..........}]".

You want to store your value fetched from hstore_to_jsonb_loose(data) -> 'entity' as full JSON ARRAY. Your data stored as value in hstore type column seems like a JSON but its not a JSON. In JSON, keys and values (other than numeric and boolean) are surrounded by " but in your string it is surrounded by '. So it can not be stored as JSON in JSONB type column.
Considering that there is no other problem in the structure of values as JSON (other than '). We should replace the ' with " and store the value as JSONB in the column. Try This query to do the same.

Alter TABLE test 
   ALTER COLUMN h_store_column TYPE jsonb 
   USING replace(hstore_to_jsonb_loose(data)->>'entity','''','"')::jsonb;

DEMO1

Even hstore_to_jsonb_loose is not required in your case. You can write your Alter statement as below:

Alter TABLE test 
   ALTER COLUMN h_store_column TYPE jsonb 
   USING replace((data)->'entity','''','"')::jsonb;

DEMO2

Transforming HSTORE array values to a column in JSONB

1 Answers