node.js - 在Postgres中，每个唯一的JSONB值选择一行(Selecting one row per unique JSONB value in Postgres)

Question

I am trying to get the newest version of data from each unique "data"->>'account' but only for a specific parent UUID.

(我正在尝试从每个唯一的"data"->>'account'获取最新版本的数据，但仅针对特定的父UUID。)

I want to be able to keep historical data as well, so updating the rows is not an option.

(我也希望能够保留历史数据，因此无法更新行。)

The data looks like this:

(数据如下所示：)

| id     | name       | parent | data                            | updated | created |
|--------|------------|--------|---------------------------------|---------|---------|
| uuid-a | First      | uuid-x | {"account": 1001, "value": 700} | date    | date    |
| uuid-b | Irrelevant | uuid-y | {"account": 9999, "value": 300} | date    | date    |
| uuid-c | Second     | uuid-x | {"account": 1002, "value": 500} | date    | date    |
| uuid-d | Third      | uuid-x | {"account": 1003, "value": 200} | date    | date    |
| uuid-e | Old        | uuid-x | {"account": 1003, "value": 100} | date    | date    |
...

I have tried using DISTINCT ON and GROUP BY to only get one row per unique "data"->>'account' , but I have not been able to write a query that works.

(我尝试使用DISTINCT ON和GROUP BY每个唯一的"data"->>'account'仅获得一行，但是我无法编写有效的查询。)

My most recent query attempt looks something like this:

(我最近的查询尝试看起来像这样：)

SELECT DISTINCT ON ("data"->>'account') * -- Select only one row per unique 'account'
FROM "table"                              -- Define the source table
WHERE "parent" = 'uuid-x'                 -- Get only rows for the correct parent
ORDER BY "created" DESC                   -- Get the newest data

The output I want from the query with the above data as an example is:

(我希望从查询中获得上述数据作为示例的输出是：)

| id     | name   | parent | data                            | updated | created |
|--------|--------|--------|---------------------------------|---------|---------|
| uuid-a | First  | uuid-x | {"account": 1001, "value": 700} | date    | date    |
| uuid-c | Second | uuid-x | {"account": 1002, "value": 500} | date    | date    |
| uuid-d | Third  | uuid-x | {"account": 1003, "value": 200} | date    | date    |

Currently I am faced with an error about ordering by "created" not being possible:

(目前，我无法通过"created"进行排序时遇到错误：)

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

I have tried to figure out why I receive this error - and how to fix it as well, but I have not been able to solve it using other resources.

(我试图弄清楚为什么会收到此错误-以及如何解决该错误，但是我无法使用其他资源来解决它。)

I am attempting to perform these actions from a NodeJS/SequelizeJS environment, but that should not matter for this specific question.

(我正在尝试从NodeJS / SequelizeJS环境中执行这些操作，但这对于这个特定问题不重要。)

Thanks in advance.

(提前致谢。)

  ask by M. Foldager translate from so

Answer 1

等待大神答复