PostgreSQL:使用'‘解析JSON时出错

Question

当我试图从PostgreSQL表中解析JSON时，你能告诉我如何修复这个错误吗？

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

我研究了这个问题，发现它是由于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}]

另一个JSON包含""：

[{"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"}]

我尝试使用以下命令进行解析：

-- 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);

Answer 1

正如@Jim-Jones所说，JSON是无效的。

有许多在线和离线JSON验证工具。我建议使用它。

它可以帮助您检测问题的原因:是无效的JSON还是代码中的错误。

例如，JSON Formatter说它替换了错误的引号。