CODEWITHSUNDEEP
jsonpostgresql
Viktor Andriichuk
Viktor Andriichuk

Reputation: 113

PostgreSQL: error with parse JSON with ''

can you get advise me how I can fix the error when I try to parse JSON from PostgreSQL table?

ERROR: invalid input syntax for type json Hint: Token "'" is invalid. Where: JSON data, line 1: [{'...

I have researched this issue and see that it comes up due to the fact that some contain '' in JSON:

[{'name':'cc','desc':'What is your credit card number? I promise to keep it real secure like.','type':'string','regex':'\\d+','min_length':1,'max_length':16,'example':'736363627'},{'name':'height','desc':'How tall are you?','type':'int','min':4,'max':666,'example':55},{'name':'likescake','desc':'Do you like cake?','type':'bool'},{'name':'address','desc':'What is your address','type':'string','min_length':5,'example':'blk 6 lot 26 blah blah'},{'name':'single','desc':'Are you single?','type':'bool'},{'name':'weight','desc':'what is your weight in kgs?','type':'float','example':55}]

another JSONs contain "":

[{"desc": "What is your credit card number? I promise to keep it real secure like.", "name": "cc", "type": "string", "regex": "\\d+", "max_length": 16, "min_length": 1}, {"max": "666", "min": "4", "desc": "How tall are you?", "name": "height", "type": "int"}, {"desc": "Do you like cake?", "name": "likescake", "type": "bool"}]

I try to parse with this command:

-- For multiple choice from JSON
SELECT
  s.projectid,
  s.prompttype,
  el.inputs->>'name' AS name,
  el.inputs->>'desc' AS desc,
  el.inputs->>'values' AS values,
  s.created,
  s.modified
FROM source_redshift.staticprompts AS s,
     jsonb_array_elements(s.inputs::jsonb) el(inputs);

Upvotes: 0

Views: 1222

Answers (1)

Sergey Zaykov
Sergey Zaykov

Reputation: 586

As @Jim-Jones said, JSON is invalid.

There are many online and offline JSON validation tools. I recomend to use it.

Its help you detect the cause of problems: is it invalid JSON or error in your code.

For example, JSON Formatter said that it replaced incorrect quotes.

Upvotes: 1

Related Questions