在PostgreSQL中，用“ in”中的“过滤”和“订单”当json值的侧面有一个数组时

发布于 2025-02-13 16:46:14 字数 950 浏览 1 评论 0原文

假设表具有下面提到字段，在这些字段中，“详细信息”列以JSON格式保持值。

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

JSON的示例详细信息可以像这样，

{
  "email": "[email protected]",
  "phoneNumber1": "+94111111111",
  "phoneNumber2": "+44111111111",
  "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

原文

Lets assume table has below mentions fields and among those fields, "details" column keeps values in JSON format.

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

example for json that keeps in details column can be like this

{
  "email": "[email protected]",
  "phoneNumber1": "+94111111111",
  "phoneNumber2": "+44111111111",
  "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
    }
  ]
}

I need to filter from locations code and same time need to order by details email.

As an example,

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

PostgreSQL 13.4

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

温柔嚣张 2025-02-20 16:46:14

您需要参考JSON数据类型的PostgreSQL文档。在这里查看：

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

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

您还将看到可以从JSON列中提取属性的功能的引用，以订购和过滤。

作为最终建议，您可能需要在数据库中创建用户定义的功能，以提取并返回JSON数据的相关部分，以便它们易于在选定语句中使用，例如以下示例...

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

You need to refer to the PostgreSQL documentation for JSON data types. Look here:

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

In those docs, you will read that the jsonb data type can be used for indexing (and so can support your filtering needs).

You will also see references to functions that can extract properties from your JSON column for use in ordering and filtering.

As a final suggestion, you may want to create user defined functions in your database to extract and return the relevant portions of your JSON data such that they become easy to use in SELECT statements, something like the following example...

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

回复收藏 0 原文

谁的年少不轻狂 2025-02-20 16:46:14

我已经使用以下提到的查询解决了此

问题，

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

因为只有在应用不同的订单条款中才能应用。

I have solved this one using below mentioned query

The query,

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

Because, The ORDER BY clause can only be applied after the DISTINCT has been applied

回复收藏 0 原文

~没有更多了~