sql - Sequelize.js postgres 横向使用

Question

我有一个带有 JSONB 字段的 postgres 表。json 包含对象数组

|  id  |                 my_json_field                |
-------------------------------------------------------
| 1234 | [{"id": 1, type: "c"}, {"id": 2, type: "v"}] |
| 1235 | [{"id": 1, type: "e"}, {"id": 2, type: "d"}] |

我需要通过 json 字段的 type 键对表进行排序/过滤。
服务器接受 id ，所以如果 id=1 - 我需要按 "c","e" 排序，如果 id=2 - 按 "v","d"
我有下一个 SQL:

LEFT JOIN LATERAL (
  SELECT elem ->> 'type' AS my_value
  FROM  jsonb_array_elements(my_json_field) a(elem)
  WHERE  elem ->> 'id' = '1'
) a ON true

这会将 my_value 字段添加到结果中，我可以使用它对表进行排序/过滤

这在控制台中工作正常，但我没有找到使用 Sequelize.js 添加它的方法

我也愿意接受任何其他解决方案，谢谢!

编辑，完整查询:

SELECT my_value FROM "main_table" AS "main_table"

LEFT OUTER JOIN ( "table2" AS "table2"
    LEFT OUTER JOIN "form_table" AS "table2->form_table" ON "table2"."id" = "table2->form_table"."table2_id")
    ON "main_table"."id" = "table2"."main_table_id"

LEFT JOIN LATERAL (
  SELECT elem ->> 'type' AS my_value
  FROM  jsonb_array_elements("table2->form_table".structure) a(elem)
  WHERE  elem ->> 'id' = '1'
) a ON TRUE

ORDER BY "my_value" DESC;

Answer 1

如果您直接使用 set 返回函数而不是在子选择中，则实际上不需要关键字 LATERAL 。

以下应该与您的查询执行相同的操作，并且不需要 LATERAL 关键字:

SELECT a.elem ->> 'type' as my_value
FROM "main_table" 
  LEFT JOIN  "table2" ON "main_table"."id" = "table2"."main_table_id"
  LEFT JOIN "form_table" AS "table2->form_table" ON "table2"."id" = "table2->form_table"."table2_id")
  LEFT JOIN jsonb_array_elements("table2->form_table".structure) a(elem)  on a.elem ->> 'id' = '1'
ORDER BY my_value DESC;

我还删除了围绕外部连接的无用括号和不为表提供新名称的别名以简化语法。

也许这允许您将查询与您的 ORM(又名“混淆层”)一起使用