Support for sub-queries in "values" helper does not work well with JSON columns

[dchekanov]

{
  type: 'insert',
  table: 'test',
  values: {
    jsonb_column: {
      type: 'I am not a function'
    }
  }
}

Results in:

insert into "test" ("jsonb_column") values ((I am not a function( )))
-- Values:
-- []

Expected:

insert into "test" ("jsonb_column") values ($1)
-- Values:
-- [
--   { "type": "I am not a function" }
-- ]

[jrf0110]

Yeah, this is pretty annoying behavior. I'm sure you could JSON.stringify() the value first. I'm open to suggestions on a way to support json values and functions

[dchekanov]

Thank you for the tip about JSON.stringify() - it worked!

About figuring out if "type" is meant to be a plain object property or a trigger to build a query - I don't have any good ideas, unfortunately. The closest to a viable solution I got is to treat values as plain objects unless a built sub-query is passed - in that case it should be merged into the parent.

const subquery = builder.sql({
  type: 'select', 
  table: 'tea_types', 
  columns: ['type'], 
  where: {tea_id: 1}
});

const insertsObject = {
  type: 'insert', 
  table: 'as_object', 
  values: {tea: {type: 'spicy'}}
};

const mergesQueries = {
  type: 'insert', 
  table: 'as_property', 
  values: {tea: subquery}
};

But that's a breaking change that requires extra processing and prevents from having a single query object.

It might be the best to warn about the magic property in the manual and suggest stringifying object values.