我们正在将代码从SQL Server数据库迁移到Postgres v16数据库
我们有一个名为“temprj.json”的示例文件，其定义如下：
temprj.json
{
"ChannelReadings": [
{
"ReadingsDto": [
{
"Si": 22.771737,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T12:57:43"
},
{
"Si": 22.734136,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:02:43"
},
{
"Si": 22.680228,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:07:43"
},
{
"Si": 22.619847,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:12:43"
},
{
"Si": 22.549753,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:17:43"
},
{
"Si": 22.486253,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:22:43"
},
{
"Si": 22.416321,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:27:43"
},
{
"Si": 22.339979,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:32:43"
},
{
"Si": 22.262115,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:37:43"
},
{
"Si": 22.197308,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:42:43"
},
{
"Si": 22.145514,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:47:43"
},
{
"Si": 22.105082,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:52:43"
},
{
"Si": 22.056599,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:57:43"
},
{
"Si": 22.0243,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T14:02:43"
},
{
"Si": 21.996859,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T14:07:43"
}
],
"ChannelId": 11
},
{
"ReadingsDto": [
{
"Si": 47.67,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T12:57:43"
},
{
"Si": 47.22,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:02:43"
},
{
"Si": 47.6,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:07:43"
},
{
"Si": 47.5,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:12:43"
},
{
"Si": 47.64,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:17:43"
},
{
"Si": 47.71,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:22:43"
},
{
"Si": 47.85,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:27:43"
},
{
"Si": 48.04,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:32:43"
},
{
"Si": 48.08,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:37:43"
},
{
"Si": 48.18,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:42:43"
},
{
"Si": 48.28,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:47:43"
},
{
"Si": 48.37,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:52:43"
},
{
"Si": 48.34,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:57:43"
},
{
"Si": 48.4,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T14:02:43"
},
{
"Si": 48.45,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T14:07:43"
}
],
"ChannelId": 14
},
{
"ReadingsDto": [
{
"Si": 11.088512570249659,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T12:57:43"
},
{
"Si": 10.911263312482156,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:02:43"
},
{
"Si": 10.982728529559426,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:07:43"
},
{
"Si": 10.89588156979372,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:12:43"
},
{
"Si": 10.876124721972,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:17:43"
},
{
"Si": 10.840173572226611,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:22:43"
},
{
"Si": 10.820314510971443,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:27:43"
},
{
"Si": 10.810096243819933,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:32:43"
},
{
"Si": 10.751362653255747,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:37:43"
},
{
"Si": 10.723286252634733,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:42:43"
},
{
"Si": 10.707037296936024,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:47:43"
},
{
"Si": 10.698006273047567,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:52:43"
},
{
"Si": 10.644282198132391,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:57:43"
},
{
"Si": 10.63333677772909,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T14:02:43"
},
{
"Si": 10.623712097975587,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T14:07:43"
}
],
"ChannelId": 17
},
{
"ReadingsDto": [
{
"Si": 2.893605,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:07:43"
}
],
"ChannelId": 12
},
{
"ReadingsDto": [
{
"Si": 3.294233,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:07:43"
}
],
"ChannelId": 13
},
{
"ReadingsDto": [
{
"Si": 3.294233,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2023-01-24T13:07:43"
}
],
"ChannelId": 16
}
],
"DeviceSerialNumber": "894339",
"RestartPointerNo": 5514732,
"NewDownloadTable": false,
"DataHashDto": "5Mckxoq42EeLHmLnimXv6A=="
}字符串
可以使用以下t_SQL代码直接访问此文件中的JSON：
SELECT
d.DeviceSerialNumber,
c.channelid,
r.[Si],
r.[Raw],
r.[TimeStamp]
FROM OPENROWSET(BULK 'C:\ChrisDev\Readings\temprj.json',
SINGLE_CLOB) AS J
OUTER APPLY OPENJSON(BulkColumn)
WITH
(
DeviceSerialNumber NVARCHAR(10) '$.DeviceSerialNumber',
ChannelReadings NVARCHAR(MAX) '$.ChannelReadings' AS JSON
) d
OUTER APPLY OPENJSON(d.ChannelReadings)
WITH
(
ChannelId INT '$.ChannelId',
Readings NVARCHAR(MAX) '$.ReadingsDto' AS JSON
) c
OUTER APPLY OPENJSON(c.Readings)
WITH (
Si DECIMAL(10, 2) '$.Si',
[Raw] INT '$.Raw',
[TimeStamp] DATETIME '$.TimeStamp'
) r型
有没有一个Postgres的等价函数Select from OpenRowset，我可以直接引用My JSON文件的内容。
或者我必须使用Copy命令将JSON加载到staging表中，然后直接从那里提取JSON？
1条答案
按热度按时间7hiiyaii1#
你正在寻找的是一个Foreign Data Wrapper (FDW)。具体来说，你会想要一个基于文件的，理解json的。所有可用的扩展名中的wiki has a list（或者说，人们记录的那些）。那里列出了一个json文件 Package 器，但我从来没有使用过它，所以不能说它有多容易。而且你在Windows上，所以你可能没有合适的开发工具来编译任何需要编译的东西。
所以，除非这是一个你想要自动化的常规操作，或者有问题的文件非常庞大，否则我只需要将它导入到一个值中，然后从数据库中提取你想要的东西。
还要注意的是，PostgreSQL服务器进程几乎肯定没有从你的用户帐户读取文件的权限（这是一件好事）。如果你想在服务器端访问文件，你需要先把它们放在文件系统上更全局可访问的地方，或者改变所有者。