Import JSON to Database

Question

I have a json file, and I’d like to import it into a database. Is there any way to automate this process? There is no preference for database, but if you had any function for Postgresql would be better.

Answer 1

Starting with version 9.2 of Postgressql the type was introduced JSON, that supports data storage and manipulation in JSON format.

You can use the function pg_read_file() to read text files stored on data_directory from the server in order to automate data import.

Filing cabinet paises.json:

[
    { "nome": "Brasil",    "lingua": "Portugues", "moeda": "Real"  },
    { "nome": "Portugal",  "lingua": "Portugues", "moeda": "Euro"  },
    { "nome": "Argentina", "lingua": "Espanhol",  "moeda": "Peso Argentino"  },
    { "nome": "Mexico",    "lingua": "Espanhol",  "moeda": "Peso Mexicano"  },
    { "nome": "Franca",    "lingua": "Frances",   "moeda": "Euro"  },
    { "nome": "Alemana",   "lingua": "Alemao",    "moeda": "Euro"  },
    { "nome": "EUA",       "lingua": "Ingles",    "moeda": "Dolar"  }
]

Data Structure:

CREATE TABLE tb_pais
(
    cod INTEGER,
    info JSON
);

Importing JSON File:

INSERT INTO tb_pais VALUES ( 1, pg_read_file('/var/lib/pgsql/data/paises.json')::JSON );

Recovers all countries using 'Euro' as currency:

SELECT
    json.data->>'nome'
FROM
    (SELECT json_array_elements(info) AS data FROM tb_pais) AS json
WHERE
    json.data->>'moeda' = 'Euro'

Recovers all the Spanish-speaking countries':

SELECT
    json.data->>'nome' AS nome
FROM
    (SELECT json_array_elements(info) AS data FROM tb_pais) AS json
WHERE
    json.data->>'lingua' = 'Espanhol';

Answer 2

On sql server you can import, I don’t know what your "automatically" looks like (when to put it in a folder? when to receive an email? when to call a function?). But this link explains several ways to import json to sql server

https://docs.microsoft.com/pt-br/sql/relational-databases/json/import-json-documents-into-sql-server