CODEWITHSUNDEEP
jsonpostgresql
Madeleine
Madeleine

Reputation: 2182

Querying postgres SQL JSON table

I have a JSON column (named pracInfo) in a postgres DB that holds data in the following format:

[{"ApplicationId":["123455667"],"Domain":["WORKGROUP"],"PracName":["ABC Corp cc"],"Phone":["011 123 4567"],"EMail":["[email protected]"],"SQLversion":["Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) \n\tNov 24 2008 13:01:59 \n\tCopyright (c) 1988-2005 Microsoft Corporation\n\tExpress Edition on Windows NT 6.1 (Build 7601: Service Pack 1)\n"],"ABCversion":["0000"],"ABCDBversion":["2.604"],"ABCDDBversion":["1.0.3"]}]

I'd like to pull a query that splits out the JSON properties e.g. ApplicationId, Domain, PracName etc as different columns. Where the column has two objects within the array, i'd like that to be split over two rows.

I've tried various query formats but the results keep returning blank, for example json_populate_recordset e.g.

CREATE type json_type AS (PracName varchar(100),Email Varchar(100));

SELECT id, (json_populate_recordset(null::json_type, "pracInfo")).* FROM public."PracInfos";

What am I doing wrong?

Upvotes: 0

Views: 237

Answers (1)

Clément Prévost
Clément Prévost

Reputation: 8630

You need to escape the type names with double quotes to account for case sensitivity. I also removed the length limitation that doesn't need to be set in PostgreSQL:

CREATE type json_type AS ("PracName" varchar, "Email" varchar);

Upvotes: 1

Related Questions