主页 / dba / 问题 / 282727
Accepted
vikrant rana
Asked: 2021-01-08 02:24:56 +0800 CST

从 Postgres 中的文本列解析 JSON 数据

  • 772

我JSON在一个文件中如下：

[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}

我已将其加载到一个临时表中：

create table temp_json (values text);
\copy temp_json from '/home/xyz/cgs_test.json';

现在我想将这些数据提取到单独的列中：

technology 
vendor
name
temporal_unit
regional_unit
dataset_metadata
rk

dataset_metadata是 JSON 列，其他列是字符串。

postgresql

2 个回答

  1. Best Answer

    为了回答这个问题，我做了以下事情（见 fiddle here）：

    这对我来说是一次很好的学习经历（顺便说一句，+1），所以我将按照我的逻辑完成这些步骤，希望这会对你有所帮助——但我真的是为自己做的 :-)

    步骤1：

    CREATE TABLE temp_1 
(
  json_input TEXT NOT NULL
);

    第2步：

    用你的两条记录填充它。但是，您的两条记录不是正确的 JSON - 它们[在开头和]结尾都需要 a 才能使它们成为正确的 JSON - 所以，我已将它们放在INSERT流程的一开始 - 我离开了由读者来试验如果你把它们排除在外会发生什么——你可以在这个过程的后面把它们放进去——见下文。

    可能有一种方法不必使用不同的JSON功能来做到这一点 - 恐怕超出了我的薪酬等级 - 但我会很感激任何改进建议。

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

    然后，将字符串转换为 JSON，如下所示 - 这可以批量完成：

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

    第 3 步：

    创建另一个表：

    CREATE TABLE temp_2
(
  final_json JSON NOT NULL
);

    第4步：

    通过以下方式填充：

    INSERT INTO temp_2 SELECT json_input::JSON FROM temp_1;

    ::JSON强制转换是必要的，否则将像typeINSERT一样失败。json_inputTEXT

    然后，清除“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;

    第 5 步：

    创建一个表来保存数据：

    CREATE TABLE json_table
(
  technology TEXT,
  vendor TEXT,
  name TEXT,
  temporal_unit TEXT,
  regional_unit TEXT,
  dataset_metadata JSON,
  rk SMALLINT
);

    第 6 步：

    从这里，我获得了这个方法来填充表格：

    INSERT INTO json_table 
SELECT (json_populate_recordset(null::json_table, final_json)).* FROM temp_2;

    小提琴中还有另一种方式 - 从这里- YMMV？

    第 7 步 - 最终检查：

    SELECT * FROM json_table;

    结果：

    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

    几个想法：

    • 您的dataset_metadata字段似乎不是“正确的” JSON - 可能是合适的REGEXP_REPLACE和/或CAST可能是有序的？我不知道您获取/home/xyz/cgs_test.json文件的过程 - 但可能值得看看记录是如何生成的 - 理想情况下，您应该尽早在管道中纠正任何问题。

    • 我不知道你的确切情况，但你应该仔细考虑这里的建议——你可能想把你的元数据放到一个单独的表中，并将其FOREIGN KEY连接到父记录？

    • 5

  2. COPY 并不打算按原样将每一行填充到列中。它会破坏嵌套 json 编码中的一些行格式。

    您可以使用管理功能代替 COPYpg_read_file来读取文件，然后按行拆分此文本：

    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;

    所以我们看到给定的 JSON 是正确的。让我们根据需要重新格式化：

    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

    您还可以将 dataset_metadata 中的嵌套 JSON 转换为 JSON 数据类型，并根据需要进一步处理。例如，

    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"
    • 4

相关问题