Updating A Nested Field Inside A JSONB Column

[asim-sansi]

I have an assets table that keeps a jsonb rental_history column. The column has a fixed structure and looks like this

{
  "hours":{
	"m3":0,
	"m6":0,
	"all":0,
	"m12":0
  },
  "income":{
	"m3":0,
	"m6":0,
	"all":0,
	"m12":0
  }
}

I'm trying to update the 'all' field in both income & hours sub fields. What I have come up with so far is this

const cs = new pgp.helpers.ColumnSet(
	[
		"?id",
		{
			name: "rental_history",
			mod: ":raw",
			init: (c) =>
				`jsonb_set(rental_history, '{income,all}', '{"all": ${c.source.income}}'::jsonb)`,
		},
	],
	{
		table: { table: "assets" },
	}
);
const query = pgp.helpers.update(updateArray, cs) + "WHERE uuid(v.id) = t.id";

The updateArray looks like this

[
  {
      id: '9cb51370-8474-4608-9aa5-cccdec53e9c7',
      income: 105,
      hours: 4605
  },...
]

When I run the script it returns this error
column "rental_history" does not exist

Any help would be appreciated ? Thanks

Version : "pg-promise": "^11.0.2"

[vitaly-t]

Your input data is missing the column, hence the error. Perhaps try this:

const cs = new pgp.helpers.ColumnSet(
	[
		"?id",
		{
			name: 'rental_history',
			mod: ':raw',
                        def: null, // provide a default for when the column is missing
			init: (c) =>
				`jsonb_set(rental_history, '{income,all}', '{"all": ${c.source.income}}'::jsonb)`,
		},
	],
	{
		table: { table: 'assets' }
	}
);

[asim-sansi]

I tried but it results in the same error. The query returned from pgp.helpers.update looks something like this

UPDATE "assets" AS t SET "rental_history"=v."rental_history" 
FROM (VALUES
      ('c54e6b2e-f1a3-46ca-894c-ef6d106ba5de',jsonb_set(rental_history, '{income,all}', '{"all": 0}'::jsonb)),
      ('379a5261-1794-4764-851b-3bf40492f4e8',jsonb_set(rental_history, '{income,all}', '{"all": 0}'::jsonb)))
AS v("id","rental_history") WHERE uuid(v.id) = t.id

I believe it isn't liking the "rental_history" reference inside the jsonb_set function since the input data does not have a rental_history object. Providing def: null did not help. Maybe what we want is to reference the t."rental_history" column inside the values and not v."rental_history" ?

[asim-sansi]

Hmmm Providing an empty rental_history object inside the updateArray also didn't help. Still returning the same error.

data.forEach((element) => {
  updateArray.push({
    id: element.asset_id,
    income: element.income,
    hours: element.hours,
    rental_history: {},
  });
});

[vitaly-t]

Maybe your use of rental_history inside jsonb_set is incorrect, maybe it needs to be in quotes or something. I don't know. You do not provide any reproducible example, so I cannot help any further.