CODEWITHSUNDEEP
jsonpostgresql
rachid
rachid

Reputation: 2486

load formatted json to postgreSQL table

I want to load a formatted JSON file, in the form of

{
"EId":"104111",
"Category":"(0)",
"Mac":"ABV",
"Path":"chemin2",
"ID":"System.Byte"
}

by creating first a temporary table with a json column,

 create temporary table temp_json (values json);
 copy temp_json from '/path_to_the_file/test.json';
 select values->>'EId' as EId,
       values->>'Category' as Category,
       values->>'Mac' as Mac,
       values->>'Path' as Path,
       values->>'ID' as ID      
  from(
          select json_array_elements(values) as values 
          from temp_json
  ) a;

but it shows the following message:

ERROR:  invalid input syntax for type JSON
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: {
COPY temp_json, line 1, column values: "{"

once I erase all the whitespace, the instruction pass with no error.

Upvotes: 4

Views: 2611

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

Assuming a file like this:

{
"EId":"104111",
"Category":"(0)",
"Mac":"ABV",
"Path":"chemin2",
"ID":"System.Byte"
}
{
"EId":"104112",
"Category":"(1)",
"Mac":"CBV",
"Path":"chemin3",
"ID":"System.Byte"
}

The temporary table will receive text not json:

create temporary table temp_json (values text);
\copy temp_json from '/path_to/input.json';

The definitive table will have a json column:

create table t (obj jsonb);

Some string manipulation:

insert into t (obj)
select
    regexp_split_to_table(
        replace(v, $$"}{"$$, $$"}djue748wBc,l;09{"$$),
        'djue748wBc,l;09'
    )::jsonb
from (
    select string_agg(values, '') as v
    from temp_json
) s;
                                            obj                                             
--------------------------------------------------------------------------------------------
 {"ID": "System.Byte", "EId": "104111", "Mac": "ABV", "Path": "chemin2", "Category": "(0)"}
 {"ID": "System.Byte", "EId": "104112", "Mac": "CBV", "Path": "chemin3", "Category": "(1)"}

Upvotes: 3

Evan Carroll
Evan Carroll

Reputation: 1

I don't think you're properly quoting this. See the docs on quoting, and on copy.

It's certainly possible,

CREATE TEMPORARY TABLE foo
AS
  SELECT $${
    "EId":"104111",
    "Category":"(0)",
    "Mac":"ABV",
    "Path":"chemin2",
    "ID":"System.Byte"
  }$$::jsonb AS jsondata;

Upvotes: 1

Related Questions