How to convert certain group of columns to JSON?

Asked

Viewed 376 times

1

I have the following problem:

I have a table with N columns, however, I would like to turn almost all of them into a single json column, e.g.:

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

I looked at the documentation, and I found a command that does something similar put in the whole table:

  SELECT
        array_to_json(array_agg(tab_1))
  FROM 
        tab_1

Does anyone have any idea how I might be solving this?

1 answer

1


If using Postgres 9.3:

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

Example in SQL Fiddle.

In the Postgres 9.4+ gets easier:

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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.