postgresql 22 P02错误:Postgres中json -> JSON类型的输入语法无效[已关闭]

3vpjnl9f  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(171)

**已关闭。**此问题为not reproducible or was caused by typos。目前不接受回答。

这个问题是由错字或无法再重现的问题引起的。虽然类似的问题在这里可能是on-topic,但这个问题的解决方式不太可能帮助未来的读者。
8小时前关闭
Improve this question
我试图查询一个JSON文件,以提取字段出来,并有问题。
这是我引用的JSON文件14.Json

  1. {"ChannelReadings": [
  2. { "ReadingsDto": [
  3. {
  4. "Si": 47.67,
  5. "Raw": 0,
  6. "Conversion": 0,
  7. "TimeStamp": "2023-01-24T12:57:43"
  8. },
  9. {
  10. "Si": 47.22,
  11. "Raw": 0,
  12. "Conversion": 0,
  13. "TimeStamp": "2023-01-24T13:02:43"
  14. },
  15. {
  16. "Si": 47.6,
  17. "Raw": 0,
  18. "Conversion": 0,
  19. "TimeStamp": "2023-01-24T13:07:43"
  20. },
  21. {
  22. "Si": 47.5,
  23. "Raw": 0,
  24. "Conversion": 0,
  25. "TimeStamp": "2023-01-24T13:12:43"
  26. }
  27. ],
  28. "ChannelId": 14
  29. },
  30. {
  31. "ReadingsDto": [
  32. {
  33. "Si": 2.893605,
  34. "Raw": 0,
  35. "Conversion": 0,
  36. "TimeStamp": "2023-01-24T13:07:43"
  37. }
  38. ],
  39. "ChannelId": 12
  40. },
  41. {
  42. "ReadingsDto": [
  43. {
  44. "Si": 3.294233,
  45. "Raw": 0,
  46. "Conversion": 0,
  47. "TimeStamp": "2023-01-24T13:07:43"
  48. }
  49. ],
  50. "ChannelId": 13
  51. },
  52. {
  53. "ReadingsDto": [
  54. {
  55. "Si": 3.294233,
  56. "Raw": 0,
  57. "Conversion": 0,
  58. "TimeStamp": "2023-01-24T13:07:43"
  59. }
  60. ],
  61. "ChannelId": 16
  62. }
  63. ],
  64. "DeviceSerialNumber": "894339",
  65. "RestartPointerNo": 5514732,
  66. "NewDownloadTable": false,
  67. "DataHashDto": "5Mckxoq42EeLHmLnimXv6A=="
  68. }

字符串
我可以使用以下代码成功地加载此

  1. DROP TABLE IF EXISTS tmp;
  2. CREATE TEMP table tmp (c TEXT );
  3. COPY tmp FROM 'C:\ChrisDev\Readings\14.json' WITH (FORMAT TEXT, DELIMITER '~');


当我运行以下代码时:

  1. select
  2. c::json ->> 'DeviceSerialNumber' as SerialNumber,
  3. (c::json ->> 'RestartPointerNo')::int as RestartPointerNo
  4. from tmp


我得到以下错误:

  1. [2023-11-16 23:42:08] [22P02] ERROR: invalid input syntax for type json
  2. [2023-11-16 23:42:08] Detail: The input string ended unexpectedly.
  3. [2023-11-16 23:42:08] Where: JSON data, line 1: {"ChannelReadings": [

Question我需要在这里做什么更改才能提取DeviceSerialNumber和RestartPointerNo的值

gk7wooem

gk7wooem1#

所以,按照尼克的建议,我从我的输入文件中删除了所有的CR LF代码,它工作了!

相关问题