PostgreSQL: row_to_json with selective columns

Question

Task concept and my question

Using Postgres 9.4. How could I use row_to_json(row) with selective columns (not the entire row)? I need to discard one column from the row constructor while building JSON, but also need to preserve column names.

Restrictions

Do not use self join to the same table/cte with selective columns choice
Do not use external function to handle deleting key from json, afterwards

I'm well aware that I can write and use my own function to remove a key from JSON, or that in Postgres 9.5 there is - operator for JSONB. However, I would like to do this beforehand without additional function call and I'm pretty sure it's possible.

MVCE and explanation

Generating sample data

CREATE TABLE test_table ( id int, col1 int, col2 int, col3 text );
INSERT INTO test_table VALUES 
  (1, 23, 15, 'Jessica'), (2, 43, 84, 'Thomas');

1) First try, simple row_to_json(row), which is obviously not working:

SELECT id, row_to_json(t) FROM test_table t

I need to discard column id from the row constructor not to add it while parsing the row as json. Above returns:

 id |                  row_to_json
----+-----------------------------------------------
  1 | {"id":1,"col1":23,"col2":15,"col3":"Jessica"}
  2 | {"id":2,"col1":43,"col2":84,"col3":"Thomas"}

2) Second try, with explicit passing of columns row_to_json(row(col1, ...)):

SELECT id, row_to_json(row(col1, col2, col3)) FROM test_table t

But I'm losing column names (as mentioned in docs it all converts to fX, where X is a number:

 id |           row_to_json
----+----------------------------------
  1 | {"f1":23,"f2":15,"f3":"Jessica"}
  2 | {"f1":43,"f2":84,"f3":"Thomas"}

Expected output

Expected output is obviously from the (1) point in MVCE but without id key-value pair:

 id |                  row_to_json
----+-----------------------------------------------
  1 | {"col1":23,"col2":15,"col3":"Jessica"}
  2 | {"col1":43,"col2":84,"col3":"Thomas"}

`json_build_object('col1', col1, 'col2', col2, 'col3', col3)` — , Oct 14 '16 at 11:52
@a_horse_with_no_name I'm aware of that, thanks, but I'd rather not type it all manually - my real case scenario has around 100 columns :-) — Kamil Gosciminski, Oct 14 '16 at 11:53
@pozs thank you :-) I somehow missed that one during searching. I've marked the question as duplicate. — Kamil Gosciminski, Oct 14 '16 at 12:07
```SELECT id, row_to_json(t)::jsonb-'id' FROM test_table t``` Starting with v10 in postgresql you can delete a key value from the left operand, which will return exactly your required result. — Valentin, Aug 23 '22 at 08:01

Kamil Gosciminski · Accepted Answer · 2018-03-12T15:30:27.817

13

It seems that creating a type with desired column names and matching data types and then casting the row to it will do the trick:

CREATE TYPE my_type AS (
  col1 int,
  col2 int,
  col3 text
);

Then altering my statement by adding the cast of row to defined type:

SELECT id, row_to_json(cast(row(col1, col2, col3) as my_type)) FROM test_table t;

Brings out the expected output:

 id |                  row_to_json
----+-----------------------------------------------
  1 | {"col1":23,"col2":15,"col3":"Jessica"}
  2 | {"col1":43,"col2":84,"col3":"Thomas"}

However, is there any method for this to be built without additional type?

edited Mar 12 '18 at 15:30

answered Oct 14 '16 at 11:51

Kamil Gosciminski

16,547
8
49
72

@a_horse_with_no_name This I believe would add additional step to the solution, since `json_populate_record` takes JSON as an argument. – Kamil Gosciminski Oct 14 '16 at 11:59
3

```SELECT id, row_to_json(t)::jsonb-'id' FROM test_table t``` Starting with v10 in postgresql you can delete a key value from the left operand, which will return exactly your required result. – Valentin Aug 23 '22 at 07:58

PostgreSQL: row_to_json with selective columns

1 Answers1