json - 如何处理包含 json/array 数据的 postgres 文本列
问题描述
我有一个包含车辆数据的 postgres（版本 11）表。数据从 CSV 文件中填充。名为 mot_history 的字段之一是文本字段，有时为 NULL 或具有“没有可用的 MOT 数据”的值，但通常如下所示：
[{"completedDate": "2019-05-08T13:38:21+00:00", "expiryDate": "2020-11-07T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 89178}, "passed": true, "rfcAndComments": [{"text": "Nearside Non obligatory mirror damaged ()", "type": "ADVISORY"}]}, {"completedDate": "2019-04-29T08:39:45+00:00", "expiryDate": null, "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 88909}, "passed": false, "rfcAndComments": [{"text": "Nearside Front Tyre has a cut in excess of the requirements deep enough to reach the ply or cords (5.2.3 (d) (i))", "type": "FAIL"}, {"text": "Offside Front Tyre has a cut in excess of the requirements deep enough to reach the ply or cords (5.2.3 (d) (i))", "type": "FAIL"}, {"text": "Nearside Rear Tyre has a cut in excess of the requirements deep enough to reach the ply or cords (5.2.3 (d) (i))", "type": "FAIL"}, {"text": "Nearside Obligatory mirror seriously damaged, affecting the rear view (3.3 (b) (ii))", "type": "FAIL"}]}, {"completedDate": "2018-05-01T14:33:15+00:00", "expiryDate": "2019-04-30T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 77880}, "passed": true, "rfcAndComments": [{"text": "Nearside Obligatory mirror damaged, but not seriously affecting the rear view (8.1.2a)", "type": "ADVISORY"}, {"text": "Offside Rear jacking point missing", "type": "ADVISORY"}]}, {"completedDate": "2017-04-26T08:41:07+00:00", "expiryDate": "2018-04-26T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 66658}, "passed": true, "rfcAndComments": [{"text": "Nearside Non obligatory mirror damaged ()", "type": "ADVISORY"}, {"text": "Windscreen has damage to an area less than a 40mm circle outside zone 'A' (8.3.1d)", "type": "ADVISORY"}]}, {"completedDate": "2016-04-27T09:46:44+00:00", "expiryDate": "2017-04-26T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 54490}, "passed": true, "rfcAndComments": [{"text": "Nearside Obligatory mirror damaged, but not seriously affecting the rear view (8.1.2a)", "type": "ADVISORY"}, {"text": "Front brake disc worn, pitted or scored, but not seriously weakened (3.5.1i)", "type": "ADVISORY"}, {"text": "brake warning lamp illuminated", "type": "ADVISORY"}]}, {"completedDate": "2015-04-20T10:45:28+00:00", "expiryDate": "2016-04-19T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 42143}, "passed": true, "rfcAndComments": [{"text": "Windscreen has damage to an area less than a 10mm circle within zone 'A' (8.3.1a)", "type": "ADVISORY"}, {"text": "nearside obligatory mirror seriously damaged, affecting the rear view", "type": "USER ENTERED"}, {"text": "parking brake noisy on application", "type": "USER ENTERED"}]}, {"completedDate": "2015-04-15T12:42:31+00:00", "expiryDate": null, "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 41921}, "passed": false, "rfcAndComments": [{"text": "Nearside Inner Front Brake pad(s) less than 1.5 mm thick (3.5.1g)", "type": "FAIL"}, {"text": "Nearside Inner Rear Brake pad(s) less than 1.5 mm thick (3.5.1g)", "type": "FAIL"}, {"text": "Windscreen has damage to an area less than a 10mm circle within zone 'A' (8.3.1a)", "type": "ADVISORY"}, {"text": "nearside obligatory mirror seriously damaged, affecting the rear view", "type": "USER ENTERED"}, {"text": "parking brake noisy on application", "type": "USER ENTERED"}]}, {"completedDate": "2014-04-09T17:17:55+00:00", "expiryDate": "2015-04-08T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 29857}, "passed": true, "rfcAndComments": [{"text": "Rear Tyre worn close to the legal limit (4.1.E.1)", "type": "ADVISORY"}, {"text": "both front tyres worn on inner edges", "type": "USER ENTERED"}]}]
[{"completedDate": "2017-06-22T07:40:58+00:00", "expiryDate": "2018-07-01T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 23985}, "passed": true, "rfcAndComments": []}]
[{"completedDate": "2020-03-05T12:11:13+00:00", "expiryDate": "2021-03-25T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 58804}, "passed": true, "rfcAndComments": []}, {"completedDate": "2019-03-26T13:56:21+00:00", "expiryDate": "2020-03-25T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 53674}, "passed": true, "rfcAndComments": []}, {"completedDate": "2018-10-27T10:39:06+00:00", "expiryDate": "2019-10-29T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 52580}, "passed": true, "rfcAndComments": [{"text": "Rear Brake pad(s) wearing thin 1.5 mm (1.1.13 (a) (ii))", "type": "ADVISORY"}, {"text": "Nearside Front Tyre slightly damaged/cracking or perishing Tyre cracking. (5.2.3 (d) (ii))", "type": "ADVISORY"}, {"text": "Offside Front Tyre slightly damaged/cracking or perishing Tyre cracking. (5.2.3 (d) (ii))", "type": "ADVISORY"}, {"text": "Nearside Front Tyre worn close to legal limit/worn on edge 2.5 mm (5.2.3 (e))", "type": "ADVISORY"}, {"text": "Offside Front Tyre worn close to legal limit/worn on edge 2.9 mm (5.2.3 (e))", "type": "ADVISORY"}, {"text": "Nearside Front Outer Tyre worn close to legal limit/worn on edge Worn outer edge. (5.2.3 (e))", "type": "ADVISORY"}]}]
这是上面稍微格式化的内容，可以让您更好地了解结构是什么
[
{"completedDate": "2019-05-08T13:38:21+00:00", "expiryDate": "2020-11-07T00:00:00+00:00", "testNumber": 9999999999, "odomoter": {"unit": "mi", "value": 89178}, "passed": true, "rfcAndComments":
[
{"text": "Nearside Non obligatory mirror damaged ()", "type": "ADVISORY"}
]
},
{"completedDate": "2019-04-29T08:39:45+00:00", "expiryDate": null, "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 88909}, "passed": false, "rfcAndComments": [{"text": "Nearside Front Tyre has a cut in excess of the requirements deep enough to reach the ply or cords (5.2.3 (d) (i))", "type": "FAIL"}, {"text": "Offside Front Tyre has a cut in excess of the requirements deep enough to reach the ply or cords (5.2.3 (d) (i))", "type": "FAIL"}, {"text": "Nearside Rear Tyre has a cut in excess of the requirements deep enough to reach the ply or cords (5.2.3 (d) (i))", "type": "FAIL"}, {"text": "Nearside Obligatory mirror seriously damaged, affecting the rear view (3.3 (b) (ii))", "type": "FAIL"}]},
{"completedDate": "2018-05-01T14:33:15+00:00", "expiryDate": "2019-04-30T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 77880}, "passed": true, "rfcAndComments": [{"text": "Nearside Obligatory mirror damaged, but not seriously affecting the rear view (8.1.2a)", "type": "ADVISORY"}, {"text": "Offside Rear jacking point missing", "type": "ADVISORY"}]},
{"completedDate": "2017-04-26T08:41:07+00:00", "expiryDate": "2018-04-26T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 66658}, "passed": true, "rfcAndComments": [{"text": "Nearside Non obligatory mirror damaged ()", "type": "ADVISORY"}, {"text": "Windscreen has damage to an area less than a 40mm circle outside zone 'A' (8.3.1d)", "type": "ADVISORY"}]},
{"completedDate": "2016-04-27T09:46:44+00:00", "expiryDate": "2017-04-26T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 54490}, "passed": true, "rfcAndComments": [{"text": "Nearside Obligatory mirror damaged, but not seriously affecting the rear view (8.1.2a)", "type": "ADVISORY"}, {"text": "Front brake disc worn, pitted or scored, but not seriously weakened (3.5.1i)", "type": "ADVISORY"}, {"text": "brake warning lamp illuminated", "type": "ADVISORY"}]},
{"completedDate": "2015-04-20T10:45:28+00:00", "expiryDate": "2016-04-19T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 42143}, "passed": true, "rfcAndComments": [{"text": "Windscreen has damage to an area less than a 10mm circle within zone 'A' (8.3.1a)", "type": "ADVISORY"}, {"text": "nearside obligatory mirror seriously damaged, affecting the rear view", "type": "USER ENTERED"}, {"text": "parking brake noisy on application", "type": "USER ENTERED"}]},
{"completedDate": "2015-04-15T12:42:31+00:00", "expiryDate": null, "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 41921}, "passed": false, "rfcAndComments": [{"text": "Nearside Inner Front Brake pad(s) less than 1.5 mm thick (3.5.1g)", "type": "FAIL"}, {"text": "Nearside Inner Rear Brake pad(s) less than 1.5 mm thick (3.5.1g)", "type": "FAIL"}, {"text": "Windscreen has damage to an area less than a 10mm circle within zone 'A' (8.3.1a)", "type": "ADVISORY"}, {"text": "nearside obligatory mirror seriously damaged, affecting the rear view", "type": "USER ENTERED"}, {"text": "parking brake noisy on application", "type": "USER ENTERED"}]},
{"completedDate": "2014-04-09T17:17:55+00:00", "expiryDate": "2015-04-08T00:00:00+00:00", "testNumber": 999999999, "odomoter": {"unit": "mi", "value": 29857}, "passed": true, "rfcAndComments": [{"text": "Rear Tyre worn close to the legal limit (4.1.E.1)", "type": "ADVISORY"}, {"text": "both front tyres worn on inner edges", "type": "USER ENTERED"}]}
]
我无法控制外部输入数据的格式，但我想知道是否可以使用 SQL 来获取存储在这个 JSON/array 类结构中的值。例如，假设我想返回已通过 MOT 的车辆的到期日期。我知道我可能可以通过对文本进行切片和切块来做到这一点，但想知道是否有更优雅的方式
解决方案
做一些研究，我认为这样的事情会做到这一点
with data as
(
select mot_history::jsonb as x
from my_table where length(mot_history) > 50
)
select item_object->'passed',item_object->'expiryDate',position from data,jsonb_array_elements(x) with ordinality arr(item_object, position)
where item_object->'passed' = 'true'
推荐阅读
- ios - 如何清理 xcode 构建缓存？
- exchangewebservices - 使用 EWS 更新 Outlook.com 中的日历名称不会在线注册
- c# - C#如何从对象转换为名称可用作字符串的类型
- java - 即使图像在我指定的路径中，图像图标也会返回空指针异常
- uwp-xaml - 如何将类型传递给 UWP 中的 ConverterParameter？
- excel - 查找 VBA 数组的长度
- android - 目的是什么以及如何在 Camera2 API 中使用 CaptureRequest.SENSOR_FRAME_DURATION？
- java - 转换 scala.collection.Seq 的 Java 对象
到 python 列表 - java - 我有一个字符串，我想在其中提取亲属关系和姓名
- android - Android 错误 - Gradle 项目同步失败