Luego de consultar expertos en esta materia, programadores de varias áreas y maestros hemos dado con la respuesta a la pregunta y la plasmamos en esta publicación.
Solución:
Resulta que hay una manera fácil de importar un objeto JSON de varias líneas en una columna JSON en una base de datos de Postgres mediante la herramienta de línea de comandos psql, sin necesidad de incrustar explícitamente el JSON en la instrucción SQL. La técnica está documentada en los documentos de postgresql, pero está un poco oculta.
El truco es cargar el JSON en una variable psql usando acentos graves. Por ejemplo, dado un archivo JSON de varias líneas en /tmp/prueba.json como:
"dog": "cat",
"frog": "frat"
Podemos usar el siguiente SQL para cargarlo en una tabla temporal:
sql> set content `cat /tmp/test.json`
sql> create temp table t ( j jsonb );
sql> insert into t values (:'content');
sql> select * from t;
lo que da el resultado:
j
────────────────────────────────
"dog": "cat", "frog": "frat"
(1 row)
También puede realizar operaciones en los datos directamente:
sql> select :'content'::jsonb -> 'dog';
?column?
──────────
"cat"
(1 row)
Debajo de las sábanas esto es simplemente incrustando el JSON en el SQL, pero es mucho más ordenado dejar que psql realice la interpolación por sí mismo.
Puede introducir el JSON en una instrucción SQL que extrae la información y la inserta en la tabla. Si el JSON attributes tiene exactamente el nombre de las columnas de la tabla, puede hacer algo como esto:
with customer_json (doc) as (
values
('[
"id": 23635,
"name": "Jerry Green",
"comment": "Imported from facebook."
,
"id": 23636,
"name": "John Wayne",
"comment": "Imported from facebook."
]'::json)
)
insert into customer (id, name, comment)
select p.*
from customer_json l
cross join lateral json_populate_recordset(null::customer, doc) as p
on conflict (id) do update
set name = excluded.name,
comment = excluded.comment;
Se insertarán nuevos clientes, se actualizarán los existentes. La parte “mágica” es la json_populate_recordset(null::customer, doc) que genera una representación relacional de los objetos JSON.
Lo anterior asume una definición de tabla como esta:
create table customer
(
id integer primary key,
name text not null,
comment text
);
Si los datos se proporcionan como un archivo, primero debe colocar ese archivo en alguna tabla de la base de datos. Algo como esto:
create unlogged table customer_import (doc json);
Luego cargue el archivo en una sola fila de esa tabla, por ejemplo, usando el copy comando en psql (o lo que sea que ofrezca su cliente SQL):
copy customer_import from 'customers.json' ....
Luego puede usar la declaración anterior, simplemente elimine el CTE y use la tabla de etapas:
insert into customer (id, name, comment)
select p.*
from customer_import l
cross join lateral json_populate_recordset(null::customer, doc) as p
on conflict (id) do update
set name = excluded.name,
comment = excluded.comment;
En casos de big data, la forma más eficiente de importar json desde un archivo parece no importar un solo json de un archivo, sino un csv de una sola columna: una lista de json de una línea:
datos.json.csv:
"id": 23635,"name": "Jerry Green","comment": "Imported from facebook."
"id": 23636,"name": "John Wayne","comment": "Imported from facebook."
luego, bajo psql:
create table t ( j jsonb )
copy t from 'd:pathdata.json.csv'
Se agregará un registro por json (línea) en la tabla t.
La importación “copy from” se realizó para csv y, como tal, carga los datos línea por línea. Como resultado, leer un json por línea en lugar de un solo json array para ser posteriormente fraccionada, no utilizará ninguna tabla intermedia.
Más de eso, es menos probable que golpees el limitación máxima de tamaño de línea de entrada eso surgirá si su archivo json de entrada es demasiado grande.
Por lo tanto, primero convertiría su entrada en un csv de una sola columna para luego importarlo usando el comando copiar.
Puntuaciones y reseñas
Puedes añadir valor a nuestra información añadiendo tu experiencia en las críticas.