CODEWITHSUNDEEP
sqljsonpostgresql
rmmariano
rmmariano

Reputation: 976

How do I can select the column names of a table in JSON form?

I am trying to return the column names of a table in JSON, where each column name is the key and the data type is the value.

I have the following code:

SELECT jsonb_agg(json_build_object(column_name, udt_name::regtype)) AS list 
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'layer_1001'

That produces the following result:

[{"id": "integer"}, {"geom": "geometry"}, {"address": "text"}, {"start_date": "timestamp without time zone"}]

But I needed that the code produces the follow result:

{"id": "integer", "geom": "geometry", "address": "text", "start_date": "timestamp without time zone"}

Would anyone know how to do it?

Upvotes: 0

Views: 883

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28253

use either the function json_object or jsonb_object

they take two parameters, arrays of keys and values, and both must be serializable to text

SELECT 
  JSON_OBJECT(ARRAY_AGG(column_name::TEXT), ARRAY_AGG(udt_name::text)) 
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'mytable'

Upvotes: 1

Related Questions