PostgreSQL Import JSON that structured array -> array?

Question

As I googled and some read SO posts like sql - How can I import a JSON file into PostgreSQL? - Stack Overflow, I found a way to parse/import JSON that structured array -> object, such as following(a), using json_populate_recordset, or line by line object, like (b), but I want to import JSON that structured array -> array, like following(c).

(a)

'[
      {
        "id": 23635,
        "name": "Jerry Green",
        "comment": "Imported from facebook."
      },
      {
        "id": 23636,
        "name": "John Wayne",
        "comment": "Imported from facebook."
      }
]

(b)

{"id": 23635,"name": "Jerry Green","comment": "Imported from facebook."}
{"id": 23636,"name": "John Wayne","comment": "Imported from facebook."}

(c) ←← wanna parse (actually hundreds of lines not just two)

[
    [
        1621900800000,
        38642.4422973396
    ],
    [
        1621932800000,
        38192.49969227624
    ]
]

score 1 · Accepted Answer · answered May 25 '21 at 10:56

Here is an idea how you could do this.

insert into my_table (my_bigint_column, my_numeric_column)
select (ja ->> 0)::bigint,  (ja ->> 1)::numeric -- mapping expressions here
from jsonb_array_elements
(
$JSONTEXT$
[
    [
        1621900800000,
        38642.4422973396
    ],
    [
        1621932800000,
        38192.49969227624
    ]
]
$JSONTEXT$::jsonb) ja;

or, as a parameterized query

insert into my_table (my_bigint_column, my_numeric_column)
select (ja ->> 0)::bigint,  (ja ->> 1)::numeric  -- mapping expressions here
from jsonb_array_elements (:jsontext::jsonb) ja;

Hundreds of lines would not be an issue.

PostgreSQL Import JSON that structured array -> array?

1 Answers1