Question 114604 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL: Select distinct items where condition is met

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

Tags:

2

Answers


  1. 0

    Just check if it has a comma in it:

    SELECT DISTINCT "Symbol" 
FROM "MyTable"
WHERE CONTAINS("constitutents",',')
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search