Parsing JSON data from a text column in Postgres I have JSON in a file as follows:
[xyz@innolx20122 ~]$ cat cgs_test.json
{&quot;technology&quot;:&quot;AAA&quot;,&quot;vendor&quot;:&quot;XXX&quot;,&quot;name&quot;:&quot;RBNI&quot;,&quot;temporal_unit&quot;:&quot;hour&quot;,&quot;regional_unit&quot;:&quot;cell&quot;,&quot;dataset_metadata&quot;:&quot;{\&quot;name\&quot;: \&quot;RBNI\&quot;, \&quot;temporal_unit\&quot;: \&quot;hour\&quot;, \&quot;technology\&quot;: \&quot;LTE\&quot;, \&quot;is_dimension\&quot;: false, \&quot;timestamp_column_pattern\&quot;: \&quot;yyyyMMddHHmmss\&quot;, \&quot;data_type\&quot;: \&quot;PM\&quot;, \&quot;source_name\&quot;: \&quot;RBNI\&quot;, \&quot;intervals_epoch_seconds\&quot;: [[1609941600, 1609945200]], \&quot;identifier_column_names\&quot;: [\&quot;CELLID\&quot;, \&quot;CELLNAME\&quot;, \&quot;radio_frequency_band\&quot;, \&quot;ENODEBID\&quot;, \&quot;ENODEBNAME\&quot;, \&quot;SBNID\&quot;, \&quot;SITEID\&quot;, \&quot;SITENAME\&quot;, \&quot;CLUSTER_PRIORITY\&quot;, \&quot;CP_SITE\&quot;, \&quot;IBC\&quot;, \&quot;NETWORK_TIER\&quot;, \&quot;SITE_TYPE\&quot;, \&quot;T3_FLAG\&quot;, \&quot;CLUSTERID\&quot;, \&quot;CLUSTERNAME\&quot;, \&quot;REGION\&quot;, \&quot;NETWORK\&quot;], \&quot;vendor\&quot;: \&quot;ZTE\&quot;, \&quot;timestamp_column_name\&quot;: \&quot;COLLECTTIME\&quot;, \&quot;regional_unit\&quot;: \&quot;cell\&quot;}&quot;,&quot;rk&quot;:1}
{&quot;technology&quot;:&quot;AAA&quot;,&quot;vendor&quot;:&quot;XXX&quot;,&quot;name&quot;:&quot;RRCADD&quot;,&quot;temporal_unit&quot;:&quot;hour&quot;,&quot;regional_unit&quot;:&quot;cell&quot;,&quot;dataset_metadata&quot;:&quot;{\&quot;name\&quot;: \&quot;RRCADD\&quot;, \&quot;temporal_unit\&quot;: \&quot;hour\&quot;, \&quot;technology\&quot;: \&quot;AAA\&quot;, \&quot;is_dimension\&quot;: false, \&quot;timestamp_column_pattern\&quot;: \&quot;yyyyMMddHHmmss\&quot;, \&quot;data_type\&quot;: \&quot;PM\&quot;, \&quot;source_name\&quot;: \&quot;RRCADD\&quot;, \&quot;intervals_epoch_seconds\&quot;: [[1609941600, 1609945200]], \&quot;identifier_column_names\&quot;: [\&quot;CELLID\&quot;, \&quot;CELLNAME\&quot;, \&quot;radio_frequency_band\&quot;, \&quot;ENODEBID\&quot;, \&quot;ENODEBNAME\&quot;, \&quot;SBNID\&quot;, \&quot;SITEID\&quot;, \&quot;SITENAME\&quot;, \&quot;CLUSTER_PRIORITY\&quot;, \&quot;CP_SITE\&quot;, \&quot;IBC\&quot;, \&quot;NETWORK_TIER\&quot;, \&quot;SITE_TYPE\&quot;, \&quot;T3_FLAG\&quot;, \&quot;CLUSTERID\&quot;, \&quot;CLUSTERNAME\&quot;, \&quot;REGION\&quot;, \&quot;NETWORK\&quot;], \&quot;vendor\&quot;: \&quot;XXX\&quot;, \&quot;timestamp_column_name\&quot;: \&quot;COLLECTTIME\&quot;, \&quot;regional_unit\&quot;: \&quot;cell\&quot;}&quot;,&quot;rk&quot;:1}

and I have loaded this to one temp table:
create table temp_json (values text);
\copy temp_json from '/home/xyz/cgs_test.json';

Now I want to extract this data into separate columns as:
technology 
vendor
name
temporal_unit
regional_unit
dataset_metadata
rk

dataset_metadata is a JSON column and other columns are strings.
To answer this question what I did was the following (see fiddle here):
This was quite a learning experience for me (+1 for that BTW) so I'll go through the steps in my logic and hopefully this will help you - but I'm doing it for myself really :-)
Step 1:
CREATE TABLE temp_1 
(
  json_input TEXT NOT NULL
);

Step 2:
Populate it with your two records. However, your two records aren't proper JSON - they need a [ at the beginning and ] at the end in order for them to be proper JSON - so, I've put them into the INSERT at the very beginning of the process - I leave it up to the reader to experiment with what happens if you leave them out - you can put them in later in the process - see below.
There may be a way of not having to do this with a different JSON function - beyond my pay grade I'm afraid - but I'd appreciate any suggestions for improvements.
INSERT INTO temp_1 VALUES  -- strings copied from the question
('{&quot;technology&quot;:&quot;AAA&quot;,&quot;vendor&quot;:&quot;XXX&quot;,&quot;name&quot;:&quot;RBNI&quot;,&quot;temporal_unit&quot;:&quot;hour&quot;,&quot;regional_unit&quot;:&quot;cell&quot;,&quot;dataset_metadata&quot;:&quot;{\&quot;name\&quot;: \&quot;RBNI\&quot;, \&quot;temporal_unit\&quot;: \&quot;hour\&quot;, \&quot;technology\&quot;: \&quot;LTE\&quot;, \&quot;is_dimension\&quot;: false, \&quot;timestamp_column_pattern\&quot;: \&quot;yyyyMMddHHmmss\&quot;, \&quot;data_type\&quot;: \&quot;PM\&quot;, \&quot;source_name\&quot;: \&quot;RBNI\&quot;, \&quot;intervals_epoch_seconds\&quot;: [[1609941600, 1609945200]], \&quot;identifier_column_names\&quot;: [\&quot;CELLID\&quot;, \&quot;CELLNAME\&quot;, \&quot;radio_frequency_band\&quot;, \&quot;ENODEBID\&quot;, \&quot;ENODEBNAME\&quot;, \&quot;SBNID\&quot;, \&quot;SITEID\&quot;, \&quot;SITENAME\&quot;, \&quot;CLUSTER_PRIORITY\&quot;, \&quot;CP_SITE\&quot;, \&quot;IBC\&quot;, \&quot;NETWORK_TIER\&quot;, \&quot;SITE_TYPE\&quot;, \&quot;T3_FLAG\&quot;, \&quot;CLUSTERID\&quot;, \&quot;CLUSTERNAME\&quot;, \&quot;REGION\&quot;, \&quot;NETWORK\&quot;], \&quot;vendor\&quot;: \&quot;ZTE\&quot;, \&quot;timestamp_column_name\&quot;: \&quot;COLLECTTIME\&quot;, \&quot;regional_unit\&quot;: \&quot;cell\&quot;}&quot;,&quot;rk&quot;:1}'),
('{&quot;technology&quot;:&quot;AAA&quot;,&quot;vendor&quot;:&quot;XXX&quot;,&quot;name&quot;:&quot;RRCADD&quot;,&quot;temporal_unit&quot;:&quot;hour&quot;,&quot;regional_unit&quot;:&quot;cell&quot;,&quot;dataset_metadata&quot;:&quot;{\&quot;name\&quot;: \&quot;RRCADD\&quot;, \&quot;temporal_unit\&quot;: \&quot;hour\&quot;, \&quot;technology\&quot;: \&quot;AAA\&quot;, \&quot;is_dimension\&quot;: false, \&quot;timestamp_column_pattern\&quot;: \&quot;yyyyMMddHHmmss\&quot;, \&quot;data_type\&quot;: \&quot;PM\&quot;, \&quot;source_name\&quot;: \&quot;RRCADD\&quot;, \&quot;intervals_epoch_seconds\&quot;: [[1609941600, 1609945200]], \&quot;identifier_column_names\&quot;: [\&quot;CELLID\&quot;, \&quot;CELLNAME\&quot;, \&quot;radio_frequency_band\&quot;, \&quot;ENODEBID\&quot;, \&quot;ENODEBNAME\&quot;, \&quot;SBNID\&quot;, \&quot;SITEID\&quot;, \&quot;SITENAME\&quot;, \&quot;CLUSTER_PRIORITY\&quot;, \&quot;CP_SITE\&quot;, \&quot;IBC\&quot;, \&quot;NETWORK_TIER\&quot;, \&quot;SITE_TYPE\&quot;, \&quot;T3_FLAG\&quot;, \&quot;CLUSTERID\&quot;, \&quot;CLUSTERNAME\&quot;, \&quot;REGION\&quot;, \&quot;NETWORK\&quot;], \&quot;vendor\&quot;: \&quot;XXX\&quot;, \&quot;timestamp_column_name\&quot;: \&quot;COLLECTTIME\&quot;, \&quot;regional_unit\&quot;: \&quot;cell\&quot;}&quot;,&quot;rk&quot;:1}');

Then, turn the strings into JSON as follows - this can be done in bulk:
--
--  This is to turn the &quot;ordinary&quot; strings into JSON per record. If this isn't
--  done, the process will fail further down. Will work for an arbitrary number
--  of records
--

UPDATE temp_1
SET json_input = '[' || json_input || ']';

Step 3:
Create another table:
CREATE TABLE temp_2
(
  final_json JSON NOT NULL
);

Step 4:
Populate this by:
INSERT INTO temp_2 SELECT json_input::JSON FROM temp_1; 

The ::JSON cast is necessary, otherwise the INSERT will fail as json_input is of type TEXT.
Then, clear down `temp_1':
--
--  Clear down temp_1.
--  This is needed for next batch, otherwise you'll have strings beginning 
--  with '[[' and then '[[[' (endings same with multiple ']'s) and so on!
--

DELETE FROM temp_1;

Step 5:
Create a table to hold the data:
CREATE TABLE json_table
(
  technology TEXT,
  vendor TEXT,
  name TEXT,
  temporal_unit TEXT,
  regional_unit TEXT,
  dataset_metadata JSON,
  rk SMALLINT
);

Step 6:
From here, I obtained this method to populate the table:
INSERT INTO json_table 
SELECT (json_populate_recordset(null::json_table, final_json)).* FROM temp_2;

There's another way shown in the fiddle - from here - YMMV?
Step 7 - final check:
SELECT * FROM json_table;

Result:
technology  vendor  name    temporal_unit   regional_unit   dataset_metadata    rk
AAA XXX RBNI    hour    cell    &quot;{\&quot;name\&quot;: \&quot;RBNI\&quot;, \&quot;temporal_unit\&quot;: \&quot;hour\&quot;, \&quot;technology\&quot;: \&quot;LTE\&quot;, \&quot;is_dimension\&quot;: false, \&quot;timestamp_column_pattern\&quot;: \&quot;yyyyMMddHHmmss\&quot;, \&quot;data_type\&quot;: \&quot;PM\&quot;, \&quot;source_name\&quot;: \&quot;RBNI\&quot;, \&quot;intervals_epoch_seconds\&quot;: [[1609941600, 1609945200]], \&quot;identifier_column_names\&quot;: [\&quot;CELLID\&quot;, \&quot;CELLNAME\&quot;, \&quot;radio_frequency_band\&quot;, \&quot;ENODEBID\&quot;, \&quot;ENODEBNAME\&quot;, \&quot;SBNID\&quot;, \&quot;SITEID\&quot;, \&quot;SITENAME\&quot;, \&quot;CLUSTER_PRIORITY\&quot;, \&quot;CP_SITE\&quot;, \&quot;IBC\&quot;, \&quot;NETWORK_TIER\&quot;, \&quot;SITE_TYPE\&quot;, \&quot;T3_FLAG\&quot;, \&quot;CLUSTERID\&quot;, \&quot;CLUSTERNAME\&quot;, \&quot;REGION\&quot;, \&quot;NETWORK\&quot;], \&quot;vendor\&quot;: \&quot;ZTE\&quot;, \&quot;timestamp_column_name\&quot;: \&quot;COLLECTTIME\&quot;, \&quot;regional_unit\&quot;: \&quot;cell\&quot;}&quot;   1
AAA XXX RRCADD  hour    cell    &quot;{\&quot;name\&quot;: \&quot;RRCADD\&quot;, \&quot;temporal_unit\&quot;: \&quot;hour\&quot;, \&quot;technology\&quot;: \&quot;AAA\&quot;, \&quot;is_dimension\&quot;: false, \&quot;timestamp_column_pattern\&quot;: \&quot;yyyyMMddHHmmss\&quot;, \&quot;data_type\&quot;: \&quot;PM\&quot;, \&quot;source_name\&quot;: \&quot;RRCADD\&quot;, \&quot;intervals_epoch_seconds\&quot;: [[1609941600, 1609945200]], \&quot;identifier_column_names\&quot;: [\&quot;CELLID\&quot;, \&quot;CELLNAME\&quot;, \&quot;radio_frequency_band\&quot;, \&quot;ENODEBID\&quot;, \&quot;ENODEBNAME\&quot;, \&quot;SBNID\&quot;, \&quot;SITEID\&quot;, \&quot;SITENAME\&quot;, \&quot;CLUSTER_PRIORITY\&quot;, \&quot;CP_SITE\&quot;, \&quot;IBC\&quot;, \&quot;NETWORK_TIER\&quot;, \&quot;SITE_TYPE\&quot;, \&quot;T3_FLAG\&quot;, \&quot;CLUSTERID\&quot;, \&quot;CLUSTERNAME\&quot;, \&quot;REGION\&quot;, \&quot;NETWORK\&quot;], \&quot;vendor\&quot;: \&quot;XXX\&quot;, \&quot;timestamp_column_name\&quot;: \&quot;COLLECTTIME\&quot;, \&quot;regional_unit\&quot;: \&quot;cell\&quot;}&quot;   1

A couple of thoughts:

Your dataset_metadata field doesn't appear to be &quot;proper&quot; JSON - maybe a suitable REGEXP_REPLACE and/or a CAST might be in order? I don't know the process by which you get your /home/xyz/cgs_test.json file - but it may be worth looking at how the record is generated - ideally, you should correct any issues as early in the pipeline as possible.

I don't know your exact circumstances, but you should carefully consider the advice here - you might want to hive your metadata off into a separate table with a FOREIGN KEY connecting it to the parent record?