CODEWITHSUNDEEP
postgresql
Alex Nikitin
Alex Nikitin

Reputation: 931

How to turn JSON array into Postgres array in PostgreSQL?

I have following json array 

{ "21553287" : [20304602], "2971244" : [20304602,21231942,21232245], "22400866" : [20304602], "22511397" : [20304602], "1800479" : [20304602], "22152979" : [20304602,21231681,21232245], "22471714" : [20304602,21231702], "22354302" : [20304602], "22363993" : [20304602], "22360635" : [20304602,21231679], "21987748" : [20304602,21379642], "21983181" : [20304602]}

to create this array i used 

folders AS (
SELECT json_object_agg("Face":: text, r)  FROM (
SELECT array_agg("Folder") r, "Face" FROM "FaceFolder"

WHERE "Type" = 7 AND "Face" IS NOT NULL AND  "Folder" IS NOT NULL
    GROUP BY "Face") t

)

When tried to 

SELECT "@Folder" WHERE "@Folder" ANY (((TABLE folders) -> (ca."@Face"::text))::text :: int[])

I got following error 

ERROR: malformed array literal: "[20304602,20407231]"

i understand that i need to make json array to postgres array but don't know how

Upvotes: 0

Views: 240

Answers (1)

jjanes
jjanes

Reputation: 44137

One option would be to store it as the text representation of int[] in the first place, then the cast back to int[] would work.

... json_object_agg("Face":: text, r::text) ...

Of course that makes for pretty weird JSON data if you aren't using the JSON structures throughout it.

Another option is to create a helper function to do the conversion:

create function json_to_intarray(json) returns int[] immutable parallel safe language SQL as $$ 
    select array_agg(x::int) from json_array_elements_text($1) f(x)
$$;

Upvotes: 1

Related Questions