英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论