load formatted json to postgreSQL table

Question

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.

Please edit your question and add the exact SQL statement you are using. — user330315
– user330315, Commented Apr 19, 2017 at 14:21
@ClodoaldoNeto thanks, I know this information but is there way around it. meaning loading parsed formatted json data in a file ? — rachid
– rachid, Commented Apr 19, 2017 at 15:34
Does your "JSON file" contain a single JSON record, or is it expected to generate multiple rows? — Joe Love
– Joe Love, Commented Apr 19, 2017 at 16:06

Clodoaldo Neto · Accepted Answer · 2017-04-19 16:08:00Z

3

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)"}

answered Apr 19, 2017 at 16:08

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rachid Over a year ago

thanks a lot, your solution is working, though I can't seem to figure out what (replace(v, $$"}{"$$, $$"}djue748wBc,l;09{"$$), 'djue748wBc,l;09') does ?

Clodoaldo Neto Over a year ago

@ponach It is necessary to introduce a random string on which to split the objects.

Evan Carroll · Accepted Answer · 2017-04-19 14:26:29Z

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;

answered Apr 19, 2017 at 14:26

Evan Carroll

1

Collectives™ on Stack Overflow

load formatted json to postgreSQL table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related