Can't import a JSON-formatted table into PostgreSQL

Question

I've read the article "How can I import a JSON file into PostgreSQL?" I've been trying to create a table from Query Tool, but I keep getting an error message and I am a bit lost why I get the syntax error in where other people don't... (I tried to ask a question in the post above, but I'm too new to make a comment on others' post.)

I'd appreciate it if anyone can give me a hint on how to fix this.

The query I use

with customer_json (doc) as (
    values
    ('[
  {
   "consecutive":"1",
   "ticket":"#7457512",
   "category":"Temp>Support>Rule",
   "question":"Can I ask another question?",
   "answer":"answer1 yes, but only if it''s related?",
   "url":"https://tools.team.com/inbox/ticket/7457512",
   "note":"テスト",
  },

  {
   "consecutive":"2",
   "ticket":"#4408539-2",
   "category":"Automation>WF / List",
   "question":\"not equal\" vs \"not contain\",
   "answer":"answer2  filter not any vs not all of them",
   "url":"https://tools.team.com/inbox/ticket/4408539",
   "note":"-",
  },

  {
   "consecutive":"3",
   "ticket":"#4204223-2",
   "category":"Temp>Investigation>JIRA / Temp>Investigation>Sharing",
   "question":"Thank you for sharing. Will start investigation with related teams",
   "answer":"answer3 will let you know asap",
   "url":"https://tools.team.com/inbox/ticket/4204223",
   "note":"#7457512",
  },

  {
   "consecutive":"4",
   "ticket":"#4223184",
   "category":"Temp>Close>Bug",
   "question":"[NOT fixed] When the bug won''t be fixed for a while",
   "answer":"answer4 apologies about the bug with the filter",
   "url":"https://tools.team.com/inbox/ticket/4223184",
   "note":"-",
  },

  {
   "consecutive":"5",
   "ticket":"#6462310",
   "category":"Form / Forum",
   "question":"[TempSQL>Upgade/Additional] How to conduct a questionaire / customer survey",
   "answer":"answer5 \r\nNew line",
   "url":"https://tools.team.com/inbox/ticket/6462310",
   "note":"#7457512",
  },

  {
   "consecutive":"6",
   "ticket":"#4321892",
   "category":"Automation>WF",
   "question":"*confirming *",
   "answer":"answer6 あいうえお",
   "url":"https://tools.team.com/inbox/ticket/4321892",
   "note":"-",
  }
]'::json)
)
insert into customer (consecutive, ticket, category, question, answer, url, note)
select p.*
from customer_json l
  cross join lateral json_populate_recordset(null::customer, doc) as p
on conflict (id) do update 
  set consecutive = excluded.consecutive,
      ticket = excluded.ticket,
      category = excluded.category,
      question = excluded.question,
      answer = excluded.answer,
      url = excluded.url,
      note = excluded.note;

The response / error message

ERROR:  invalid input syntax for type json
LINE 3:     ('[
             ^
DETAIL:  Expected string, but found "}".
CONTEXT:  JSON data, line 8: ...inbox/ticket/7457512",
   "note":"テスト",
  }...
SQL state: 22P02
Character: 47

Others For some reason, I'd like to paste a JSON-formatted table into the query tool.

My original data is created as an Excel file, so I use formulas to format the table as a JSON style.

snakecharmerb · Accepted Answer · 2021-06-22 06:11:42Z

2

JSON does not allow a trailing comma before a closing }. You need to ensure that your JSON is well-formed.

postgres# -- invalid json causes the error
postgres# with test(a) as (values ('[{"a": 1,}]'::jsonb)) select a from test;
ERROR:  invalid input syntax for type json
LINE 1: with test(a) as (values ('[{"a": 1,}]'::jsonb)) select a fro...
                                 ^
DETAIL:  Expected string, but found "}".
CONTEXT:  JSON data, line 1: [{"a": 1,}...

postgres# -- valid JSON is accepted
postgres# with test(a) as (values ('[{"a": 1}]'::jsonb)) select a from test;
     a      
════════════
 [{"a": 1}]
(1 row)

The error message emitted by Postgres is a little misleading, visually, as the caret ^ points to the quote at the beginning of the malformed JSON string, rather than the location of the actual error. Other tools may provide more informative messages, for example jq:

$ echo '{"a": 1,}' | jq
parse error: Expected another key-value pair at line 1, column 9

edited Jun 22, 2021 at 6:11

answered Jun 20, 2021 at 18:40

snakecharmerb

57.2k13 gold badges137 silver badges200 bronze badges

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

5 Comments

Alice Over a year ago

Hi @snakecharmerb, thank you for your reply! I checked some websites including <coding.tools/json-formatter>, but it seems that the json is between "[" and "]"? So I'm assuming that this is more about the PostgreSQL query? Also, I've confirmed that the 3rd line of the accepted answer is " ('[" on the original post <stackoverflow.com/questions/39224382/…> so I'm kinda lost how I should change that to. I appreciate it if you can give me a tip with this? Thank you!

snakecharmerb Over a year ago

You get the error regardless of whether the JSON is contained in an object {...} or an array [...]. I've changed the example to show this

snakecharmerb Over a year ago

There are some other issues with the json in the question - the triple quotes (""") on some lines. How was this json generated? It looks like a Python dict that's been formatted by black...

Alice Over a year ago

Thank you for your reply again. I met some syntax errors for both double quotation marks and quotation marks in each value, so I googled how to fix it - I can't remember which page but I guess I referred to wrong pages for my case! I'll check my JSON format again, and will update how it goes. Thanks!

Alice Over a year ago

I think I understand how to format the quotes now, but I'm still confused about the original question here. PostgreSQL’s documentation <postgresql.org/docs/9.3/functions-json.html> says ‘ is needed before and after [ ] or { } and even when I remove the quotation marks, it shows the syntax error there...

Collectives™ on Stack Overflow

Can't import a JSON-formatted table into PostgreSQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related