SQL使用2D数组与”CASE WHEN”_”IN”_”THEN”_”ELSE”。

huangapple go评论87阅读模式
英文:

SQL use 2D array with "CASE WHEN" _ "IN" _ "THEN" _ "ELSE" _

问题

我正在使用Postgres和GOLANG。

基本上,我想传入一个二维数组(不一定是二维数组,也可以是结构体或JSON对象)。在第一个值匹配产品类别的情况下,添加第二个值;否则添加0。

所以,如果不是一个二维数组,我想用100表示"是",用0表示"否"。

...

  • (CASE WHEN products.category IN $1 THEN 100 ELSE 0 END)

...

$1只是一个简单的类别切片。

但我在考虑使用这样的数组:
[[1, 100], [3, 50], [4, 120]]。所以如果产品类别是1,我可以添加100,如果是3,添加50,依此类推...否则我们就添加0。

所以伪代码看起来应该是这样的...

  • (CASE WHEN products.category IN ($1).x THEN ($1).y ELSE 0 END)

我的想法是可以通过API调用来升级产品的排序值。

我已经尝试过使用unnest和一些循环的方法,但还不能完全理解,有没有关于SQL或GOLANG的建议。我真的希望将繁重的工作放在SQL查询中。

英文:

I am using postgres and GOLANG

Basically, I want to pass in a 2D array (doesn't have to be a 2D array, could be a struct or a JSON object). In the case where the first value matches a category of a product then add the second value else add 0

So, if it wasn't a 2D array and I want 100 for yes and 0 for no

...

+ (CASE WHEN products.category IN $1 THEN 100 ELSE 0 END)
...

$1 is just a simple slice of categories

But I was thinking about having an array like
[[1, 100], [3,50], [4, 120]]. So if the product category is 1 then I can add 100, if 3 then add 50 and so forth... else we just add 0

So the psuedo code would look something like...

+ (CASE WHEN products.category IN ($1).x THEN ($1).y ELSE 0 END)

The idea is that I can upgrade a sorting value of products with API calls.

I have played with unnest and tried some loop stuff but can't quite wrap my head around it, any SQL or GOLANG suggestions. I really want to keep they heavy lifting in the SQL queries

答案1

得分: 1

我已经理解了你的问题。你的查询语句使用了一个 JSON 对象,并通过左连接和 COALESCE 函数来处理。你的 API 将 JSON 对象作为字符串传递,并在查询中使用。下面是一个简化版本的查询语句:

select products.uuid, products.category, COALESCE(CAST(catquery.value as integer),0) as value from products
LEFT JOIN (
   select key as category, value as value
   from json_each_text(
      json_object($1)
)) as catquery ON products.category = CAST(catquery.category as integer)

请注意,$1 是一个占位符,用于传递 JSON 对象的字符串值。你可以将实际的 JSON 对象字符串作为参数传递给查询。希望这可以帮助到你!

英文:

I actually figured it out, but i am not using CASE WHEN IN, I am just making a table of a json_object and doing left join and COALESCE

select products.uuid, products.category, COALESCE(CAST(catquery.value as integer),0) as value from products
LEFT JOIN (
   select key as category, value as value
   from json_each_text(
      json_object('{{1,20}}')
)) as catquery ON products.category = CAST(catquery.category as integer)

So with my api I just send over the json object as a string in the format '{{1,20},{3,100}}' and then pass this string in my query, below is a simplified version of my query:

select products.uuid, products.category, COALESCE(CAST(catquery.value as integer),0) as value from products
LEFT JOIN (
   select key as category, value as value
   from json_each_text(
      json_object($1)
)) as catquery ON products.category = CAST(catquery.category as integer)

huangapple
  • 本文由 发表于 2023年4月28日 04:57:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/76124598.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定