Comment on page

Getting JSON data from PostgreSQL

Posted on 04 Sep, 2021
To extract data in json from a table without specifying column names
select to_json(t) from table t where id=34;
From docs
Converts any SQL value to json or jsonb. Arrays and composites are converted recursively to arrays and objects (multidimensional arrays become arrays of arrays in JSON). Otherwise, if there is a cast from the SQL data type to json, the cast function will be used to perform the conversion;[a] otherwise, a scalar JSON value is produced. For any scalar other than a number, a Boolean, or a null value, the text representation will be used, with escaping as necessary to make it a valid JSON string value.