I have a relation in a database as follows:

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

I want to get the distinct symbols where the constituents column contains more than 1 json in the list. So the outcome should be

GLD, KLE, MEME

My attempt is:

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

but I get an error: ERROR: cannot get array length of a non-array

CodePudding user response：

Just check if it has a comma in it:

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

CodePudding user response：

It depemds how our column is defined as JSON or JSONB, so you need to use the appropriate functions

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

Symbol
GLD
KLE
KLE
MEME

SELECT 4

fiddle

Json

CREATE TABLE T2
    ("constituents" JSON, "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 JSON_ARRAY_LENGTH("constituents") > 1

Symbol
GLD
KLE
KLE
MEME

SELECT 4

fiddle