How to popular a postgres table with JSON?

Asked

Viewed 542 times

1

mysql sql database json postgresql
  • 1

    The question is with the postgres and mysql tag, in which database are you trying to enter the data?

  • For me it can serve either of the two... Because I can use Pentaho to transfer from one table to another

1 answer

1

Assuming the data destination table is something like:

CREATE TABLE tb_foobar
(
    id BIGINT primary key,
    name TEXT
);

You can use the function json_populate_record() of Postgres to insert the data into your table from a data format JSON, look at you:

INSERT INTO tb_foobar( id, name )
(SELECT * FROM json_populate_record( NULL::tb_foobar, '{"id": 100, "name": "JOAO"}'));

INSERT INTO tb_foobar( id, name )
(SELECT * FROM json_populate_record( NULL::tb_foobar, '{"id": 200, "name": "MARIA"}'));

INSERT INTO tb_foobar( id, name )
(SELECT * FROM json_populate_record( NULL::tb_foobar, '{"id": 300, "name": "JESUS"}'));

INSERT INTO tb_foobar( id, name )
(SELECT * FROM json_populate_record( NULL::tb_foobar, '{"id": 400, "name": "MADALENA"}'));

Consulting:

SELECT id, nome FROM tb_foobar;

Exit:

|  id |     name |
|-----|----------|
| 100 |     JOAO |
| 200 |    MARIA |
| 300 |    JESUS |
| 400 | MADALENA |

Sqlfiddle: http://sqlfiddle.com/#! 17/2a753/1

EDIT:

In your case, the file JSON input has multiple records. You can use the function json_populate_recordset() combined with WITH to solve your problem.

Assuming you have a table similar to this:

CREATE TABLE tb_estados_cidades
(
    id BIGSERIAL primary key,
    sigla VARCHAR(2),
    nome TEXT
);

You can implement something like:

WITH arquivo_json( doc ) AS (
VALUES (
'[
    { "sigla": "AC", "nome": "Acre" },
    { "sigla": "AL", "nome": "Alagoas" },
    { "sigla": "AM", "nome": "Amazonas" },
    { "sigla": "AP", "nome": "Amapá" }
  ]'::json))


INSERT INTO  tb_estados_cidades ( sigla, nome )
  SELECT
    rec.sigla, rec.nome
  FROM
    arquivo_json AS arq
  CROSS JOIN 
     json_populate_recordset( NULL::tb_estados_cidades, doc ) AS rec;

Consultation:

SELECT id, sigla, nome FROM tb_estados_cidades;

Exit:

| id | sigla |     nome |
|----|-------|----------|
|  1 |    AC |     Acre |
|  2 |    AL |  Alagoas |
|  3 |    AM | Amazonas |
|  4 |    AP |    Amapá |

Sqlfiddle: http://sqlfiddle.com/#! 17/d04cf/2

  • Show! and to import a JSON file? Is there such a possibility?

Browser other questions tagged mysql sql database json postgresql

You are not signed in. Login or sign up in order to post.