How to convert certain group of columns to JSON?

Question

Navigation

#1 by (1 votes)

1

I have the following problem:

I have a table with N columns, but I would like to make almost all of them into a single column of type json, eg:

  id,
  json(col2,col3,col4) as dados

I looked at the documentation, and found a command that does something like that on the whole table:

  SELECT
        array_to_json(array_agg(tab_1))
  FROM 
        tab_1

Does anyone have any idea how I can solve this?

sql json postgresql

asked by anonymous 26.03.2016 / 01:45

1 answer

Difficulty Menu image Bootstrap I can not assign value to an ArrayList of an Object

score 1 · Accepted Answer

If you are using Postgres 9.3 :

SELECT id, row_to_json(
    (SELECT d FROM (SELECT col2, col3, col4) d)
) AS dados FROM tab_1;

Example on SQL Fiddle .

No Postgres 9.4 + is easier:

SELECT id, json_build_object(
    'Coluna 2', col2, 'Coluna 3', col3, 'Coluna 4', col4
) AS dados FROM tab_1;