在 PostgreSQL 中，当 JSON 值的一侧有一个数组时，使用 "IN" 和"Order By"进行过滤

Question

让我们假设表中有下面提到的字段，在这些字段中有"details">

| id | firstName | lastName | title | details(json) |

示例json,详细列可以是这样

{
"email": "[e-mail]",
"phoneNumber1": "[phone]",
"phoneNumber2": "[phone]",
"locations": [
{
"code": "LK",
"name": "Sri Lanka",
"lat": 128.12,
"lon": 138.23
},
{
"code": "UK",
"name": "England",
"lat": 148.12,
"lon": 158.23
},
{
"code": "IND",
"name": "India",
"lat": 163.12,
"lon": 172.23
}
]
}

我需要从位置代码过滤，同时需要通过详细的电子邮件订购。

作为一个例子，

--------------------------------
details->>'code' IN ('LK','UK') 
ORDER BY details->>'email'
--------------------------------

PostgreSQL 13.4

Answer 1

您需要参考PostgreSQL文档了解JSON数据类型。看这里:

https://www.postgresql.org/docs/current/datatype-json.html

在这些文档中，您将读到jsonb数据类型可用于索引(因此可以支持您的过滤需求)。

您还将看到对函数的引用，这些函数可以从JSON列中提取属性以用于排序和过滤。

作为最后的建议，您可能希望在数据库中创建用户定义的函数来提取并返回JSON数据的相关部分，以便在SELECT语句中易于使用，就像下面的示例一样…

SELECT 
GetEmailAddressFromJSON()
FROM table 
WHERE
IsLocationCodeFromJSON(:locationCode)
ORDER BY
GetEmailAddressFromJSON()

Answer 2

我已经解决了这个问题使用下面提到的查询

查询

SELECT firstname, lastname, details 
FROM
(
SELECT DISTINCT((person.details)::jsonb) as details, person.firstname, 
person.lastname
FROM person 
CROSS JOIN json_array_elements(person.details->'locations') AS locations
WHERE locations->>'code' IN ('LK','GB')
) as result
ORDER BY details->'email' DESC

因为，ORDER BY子句只能在应用了DISTINCT之后才能应用