sql - 复杂的 postgres json order by 子句

Question

架构:

TABLE field (
  field_id serial NOT NULL,
  section_id integer,
  title text,
  type text,
  default_val json,
  rank integer,
  tiny integer,
  "values" json,
  grp_id integer,
  aggregate integer DEFAULT 1,
  enabled integer,
  deleted integer DEFAULT 0,
  "desc" text
)

TABLE entry
(
  entry_id serial NOT NULL,
  section_id integer,
  deleted integer DEFAULT 0,
  grp_id integer,
  data json,
  last_edited bigint,
  last_editor_id integer
)

field.values 列可能如下所示:{0: {"rank": 2, "title": "asdf"}, 1: {"rank": 1 }}

entry.data 列可能如下所示:{250: 1, 251: 0}

我想查询的内容:

SELECT entry.*
FROM entry
LEFT JOIN field ON field.field_id = 31
WHERE entry.deleted = 0 AND section_id = $1 AND grp_id = $2
ORDER BY cast(field.values#>>'{**entry.data->>250**, rank}' as numeric) ASC

这是我不知道怎么做的部分:**entry.data->>250**

这可能吗？基本上，我试图按与条目对应的字段中索引的排名值进行排序。

编辑:

尝试:

(field.values->(cast(coalesce(e.data->>'f4', '0') as numeric)))->>'rank'

错误:运算符不存在:json -> 数字

Answer 1

您不要尝试编写一个字符串来解析嵌入式函数。您连接字符串以构建更大的字符串...

field.values#>>('{' || entry.data->>250 || ', rank}')

或者，也许更容易阅读？

(field.values->(entry.data->>250))->>'rank'