3

I have JSON in a file as follows:

[xyz@innolx20122 ~]$ cat cgs_test.json
{"technology":"AAA","vendor":"XXX","name":"RBNI","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RBNI\", \"temporal_unit\": \"hour\", \"technology\": \"LTE\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RBNI\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"ZTE\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}
{"technology":"AAA","vendor":"XXX","name":"RRCADD","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RRCADD\", \"temporal_unit\": \"hour\", \"technology\": \"AAA\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RRCADD\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"XXX\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":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.

2 Answers 2

5

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
('{"technology":"AAA","vendor":"XXX","name":"RBNI","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RBNI\", \"temporal_unit\": \"hour\", \"technology\": \"LTE\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RBNI\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"ZTE\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}'),
('{"technology":"AAA","vendor":"XXX","name":"RRCADD","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RRCADD\", \"temporal_unit\": \"hour\", \"technology\": \"AAA\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RRCADD\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"XXX\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}');

Then, turn the strings into JSON as follows - this can be done in bulk:

--
--  This is to turn the "ordinary" 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    "{\"name\": \"RBNI\", \"temporal_unit\": \"hour\", \"technology\": \"LTE\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RBNI\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"ZTE\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}"   1
AAA XXX RRCADD  hour    cell    "{\"name\": \"RRCADD\", \"temporal_unit\": \"hour\", \"technology\": \"AAA\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RRCADD\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"XXX\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}"   1

A couple of thoughts:

  • Your dataset_metadata field doesn't appear to be "proper" 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?

8
  • Thanks.. what's that || along with '['. also could you please suggest some way to add it via some command. can we remove manually process to add '['.. sometimes files will be huge Jan 7, 2021 at 16:45
  • Ooops - the link to the fiddle wasn't there - it is now! I'll modify it so that the process is simpler!
    – Vérace
    Jan 7, 2021 at 16:50
  • 1
    You can do UPDATE temp_1 SET json_input = '[' || json_input || ']'; and further down, you DELETE FROM temp_1 - see the revised fiddle and the explanation - this can be done in bulk! HTH!
    – Vérace
    Jan 7, 2021 at 17:00
  • Thanks a lot.. I added '[' and ']' and when I use copy command to load temp_1 table.. \copy temp_1 from '/home/xyz/cgs_test.json'; table get loaded and has removed all '\' on its own. I cannot query like this SELECT json_input::JSON FROM temp_1; Jan 7, 2021 at 17:04
  • 1
    @vikrantrana - I've put up a solution for bulk UPDATEing of the input strings. Let me know if this is satisfactory!
    – Vérace
    Jan 7, 2021 at 17:08
4

COPY is not intended to populate each line to column as-is. It will break some line formatting in nested json encode.

Instead of COPY you may use pg_read_file admin function to read the file and then split this text per-line:

create temp table temp_json as
select v::json 
 from regexp_split_to_table(
     trim(pg_read_file('/tmp/json'), E'\n'), 
 '[\n\r]+') as v;

So we see that given JSON is correct. Let's reformat as needed:

select v->>'technology', 
v->>'vendor', 
v->>'name', 
v->>'temporal_unit', 
v->>'regional_unit', 
v->>'dataset_metadata', 
v->>'rk' 
from temp_json ;

-[ RECORD 1 ]--
?column? | AAA
?column? | XXX
?column? | RBNI
?column? | hour
?column? | cell
?column? | {"name": "RBNI", "temporal_unit": "hour", "technology": "LTE", "is_dimension": false, "timestamp_column_pattern": "yyyyMMddHHmmss", "data_type": "PM", "source_name": "RBNI", "intervals_epoch_seconds": [[1609941600, 1609945200]], "identifier_column_names": ["CELLID", "CELLNAME", "radio_frequency_band", "ENODEBID", "ENODEBNAME", "SBNID", "SITEID", "SITENAME", "CLUSTER_PRIORITY", "CP_SITE", "IBC", "NETWORK_TIER", "SITE_TYPE", "T3_FLAG", "CLUSTERID", "CLUSTERNAME", "REGION", "NETWORK"], "vendor": "ZTE", "timestamp_column_name": "COLLECTTIME", "regional_unit": "cell"}
?column? | 1
-[ RECORD 2 ]-
?column? | AAA
?column? | XXX
?column? | RRCADD
?column? | hour
?column? | cell
?column? | {"name": "RRCADD", "temporal_unit": "hour", "technology": "AAA", "is_dimension": false, "timestamp_column_pattern": "yyyyMMddHHmmss", "data_type": "PM", "source_name": "RRCADD", "intervals_epoch_seconds": [[1609941600, 1609945200]], "identifier_column_names": ["CELLID", "CELLNAME", "radio_frequency_band", "ENODEBID", "ENODEBNAME", "SBNID", "SITEID", "SITENAME", "CLUSTER_PRIORITY", "CP_SITE", "IBC", "NETWORK_TIER", "SITE_TYPE", "T3_FLAG", "CLUSTERID", "CLUSTERNAME", "REGION", "NETWORK"], "vendor": "XXX", "timestamp_column_name": "COLLECTTIME", "regional_unit": "cell"}
?column? | 1

You could also cast nested JSON in dataset_metadata to JSON datatype and process it further as you wish. For example,

select v->>'name', 
  cast((v->>'dataset_metadata') as json)->'vendor' as meta_vendor
  from temp_json ;
-[ RECORD 1 ]-------
?column?    | RBNI
meta_vendor | "ZTE"
-[ RECORD 2 ]-------
?column?    | RRCADD
meta_vendor | "XXX"
3
  • Excellent answer which is much better than mine - you're correcting the problems with the input at the very first hurdle - the loading into a table part (+1)! How would you hive off the dataset_metadata part as a child table using an FK? It looks tricky because of the array identifier_column_names?
    – Vérace
    Jan 7, 2021 at 14:05
  • @Vérace, I didn't see any problems with identifier_column_names. Leave as json array, or convert to some enum array (if possible values are known) or text[] with select (select array_agg(s) from json_array_elements_text(cast((v->>'dataset_metadata') as json)->'identifier_column_names') as s) from temp_json ;
    – Melkij
    Jan 7, 2021 at 15:02
  • I am getting user must be superuser but I like answer.. both answers seems to be good Jan 7, 2021 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.