提问人：ChrisAsi71 提问时间：11/16/2023 最后编辑：ChrisAsi71 更新时间：11/16/2023 访问量：32
是否有 JSON 文件的 SQL Server 从行集打开命令的 Postgres 等效项
Is there the Postgres equivalent of the SQL Server Open from rowset command for JSON file
问：
我们正在将代码从 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
是否有 Select from OpenRowset 的 Postgres 等效函数，我可以直接引用 My JSON 文件的内容。
或者我是否必须使用复制命令将我的 JSON 加载到临时表中，然后从那里直接提取 JSON？
答：
0赞
Richard Huxton
11/16/2023
#1
您要查找的称为外部数据包装器 （FDW）。具体来说，您将需要一个理解 json 的基于文件的。wiki 列出了所有可用的扩展（或者无论如何，人们记录的扩展）。那里列出了一个 json 文件包装器，但我从未使用过它，所以不能说它有多容易。此外，您使用的是 Windows，因此您可能没有合适的开发人员工具来编译任何需要编译的内容。
所以 - 除非这将是你想要自动化的常规事情，或者有问题的文件是巨大的，否则我只会将其导入到单个值并从数据库内部提取你想要的内容。
还要注意的是，PostgreSQL服务器进程几乎肯定无权从您的用户帐户读取文件（这是一件好事）。如果要在服务器端访问文件，则需要先将它们放在文件系统上某个更全局可访问的位置，或者更改所有者。
评论
0赞
ChrisAsi71
11/16/2023
谢谢理查德...我想我们将通过将其导入临时保持表的选项
0赞
nbk
11/16/2023
这与我发布的链接相同
评论