SQL:选择满足条件的不同项目

Question

我在数据库中有一个关系，如下所示：

constituents                                                         Symbol
[{"weight":0.5, "ticker":"WPM"},{"weight":0.5, "ticker":"AEM"}]      GLD
[{"weight":0.5, "ticker":"XYZ"},{"weight":0.5, "ticker":"ABC"}]      KLE
[{"weight":1.0, "ticker":"TSLA"}]                                    TSLA
[{"weight":1.0, "ticker":"MSFT"}]                                    MSFT
[{"weight":0.4, "ticker":"XYZ"},{"weight":0.6, "ticker":"ABC"}]      KLE
[{"weight":0.3, "ticker":"BBBY"},{"weight":0.7, "ticker":"GME"}]     MEME

我想获得不同的符号，其中components列在列表中包含1个以上的json。所以结果应该是

GLD, KLE, MEME

我的尝试是：

SELECT DISTINCT "Symbol" FROM "MyTable" WHERE JSONB_ARRAY_LENGTH("constitutents")>1

但是我得到一个错误：ERROR: cannot get array length of a non-array

Answer 1

它决定了如何将我们的列定义为JSON或JSONB，因此您需要使用适当的函数

JSONB

CREATE TABLE T2
("constituents" JSONB, "Symbol" varchar(4))
;

INSERT INTO T2
("constituents", "Symbol")
VALUES
('[{"weight":0.5, "ticker":"WPM"},{"weight":0.5, "ticker":"AEM"}]', 'GLD'),
('[{"weight":0.5, "ticker":"XYZ"},{"weight":0.5, "ticker":"ABC"}]', 'KLE'),
('[{"weight":1.0, "ticker":"TSLA"}]', 'TSLA'),
('[{"weight":1.0, "ticker":"MSFT"}]', 'MSFT'),
('[{"weight":0.4, "ticker":"XYZ"},{"weight":0.6, "ticker":"ABC"}]', 'KLE'),
('[{"weight":0.3, "ticker":"BBBY"},{"weight":0.7, "ticker":"GME"}]', 'MEME')
;

CREATE TABLE

INSERT 0 6

SELECT "Symbol" FROM T2 WHERE JSONB_ARRAY_LENGTH("constituents") > 1

符号
GLD
KLE
KLE
MEME

只需检查其中是否有逗号：

SELECT DISTINCT "Symbol" 
FROM "MyTable"
WHERE CONTAINS("constitutents",',')