我试图从一些JSON中提取值,但在这方面遇到了很大的困难.
我的JSON如下:
{
"SerialNumber":"940860",
"ChannelIDs":[17,13,11,12,14],
"BeginningDate":"2023-05-20T11:53:39",
"EndDate":"2023-05-22T09:04:04"
}
我将其另存为一个名为spj2.json的文件,然后运行以下命令加载该文件:
CREATE TABLE tmp (c TEXT);
copy tmp from 'C:\temp\spj2.json'
然后,我try 使用以下内容进行查询:
select
c ->> 'SerialNumber' as SerialNumber,
c ->> 'BeginningDate' as BeginningDate,
c ->> 'EndDate' as EndDate
from tmp;
但是会收到以下错误消息
[2023-11-07 10:54:05] [22P02] ERROR: invalid input syntax for type json
[2023-11-07 10:54:05] Detail: The input string ended unexpectedly.
[2023-11-07 10:54:05] Where: JSON data, line 1: {
[2023-11-07 10:54:05] COPY tmp, line 1, column c: "{"
我想做的是从JSON中提取值,如下所示:
SerialNumber BeginningDate EndDate
940860 2023-05-20T11:53:39 2023-05-22T09:04:04
然后将如下所示的ChannelID值放入本地临时表
ID ChannelID
1 11
2 12
3 13
4 14
5 17