Início / dba / Perguntas / 282727
Accepted
vikrant rana
Asked: 2021-01-08 02:24:56 +0800 CST

Analisando dados JSON de uma coluna de texto no Postgres

  • 772

Eu tenho JSONem um arquivo da seguinte forma:

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

e eu carreguei isso para uma tabela temporária:

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

Agora eu quero extrair esses dados em colunas separadas como:

technology 
vendor
name
temporal_unit
regional_unit
dataset_metadata
rk

dataset_metadataé uma coluna JSON e outras colunas são strings.

postgresql

2 respostas

  1. Best Answer

    Para responder a esta pergunta o que eu fiz foi o seguinte (veja violino aqui ):

    Esta foi uma experiência de aprendizado para mim (+1 para esse BTW), então vou seguir as etapas da minha lógica e espero que isso ajude você - mas estou fazendo isso por mim mesmo :-)

    Passo 1:

    CREATE TABLE temp_1 
(
  json_input TEXT NOT NULL
);

    Passo 2:

    Preencha-o com seus dois registros. No entanto, seus dois registros não são JSON apropriados - eles precisam de um [no início e ]no final para que sejam JSON apropriados - então, eu os coloquei INSERTno início do processo - deixo-o cabe ao leitor experimentar o que acontece se você os deixar de fora - você pode colocá-los mais tarde no processo - veja abaixo.

    Pode haver uma maneira de não ter que fazer isso com uma JSONfunção diferente - além da minha nota salarial, temo - mas gostaria de receber sugestões de melhorias.

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

    Em seguida, transforme as strings em JSON da seguinte maneira - isso pode ser feito em massa:

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

    Etapa 3:

    Crie outra tabela:

    CREATE TABLE temp_2
(
  final_json JSON NOT NULL
);

    Passo 4:

    Preencha isso por:

    INSERT INTO temp_2 SELECT json_input::JSON FROM temp_1;

    O ::JSONcast é necessário, caso contrário o cast INSERTirá falhar como json_inputé do tipo TEXT.

    Em seguida, limpe `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;

    Etapa 5:

    Crie uma tabela para armazenar os dados:

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

    Etapa 6:

    A partir daqui , obtive este método para preencher a tabela:

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

    Há outra maneira mostrada no violino - daqui - YMMV?

    Passo 7 - verificação final:

    SELECT * FROM json_table;

    Resultado:

    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

    Um par de pensamentos:

    • Seu dataset_metadatacampo não parece ser um JSON "adequado" - talvez um adequado REGEXP_REPLACEe/ou um CASTpossa estar em ordem? Não conheço o processo pelo qual você obtém seu /home/xyz/cgs_test.jsonarquivo - mas pode valer a pena observar como o registro é gerado - idealmente, você deve corrigir quaisquer problemas o mais cedo possível no pipeline.

    • Eu não sei suas circunstâncias exatas, mas você deve considerar cuidadosamente o conselho aqui - você pode querer agrupar seus metadados em uma tabela separada com uma FOREIGN KEYconexão com o registro pai?

    • 5

  2. COPY não se destina a preencher cada linha na coluna como está. Ele quebrará alguma formatação de linha na codificação json aninhada.

    Em vez de COPY, você pode usar pg_read_filea função admin para ler o arquivo e depois dividir este texto por linha:

    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;

    Então, vemos que o JSON está correto. Vamos reformatar conforme necessário:

    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

    Você também pode converter JSON aninhado em dataset_metadata para o tipo de dados JSON e processá-lo ainda mais como desejar. Por exemplo,

    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

relate perguntas