Function jsonBuildObject

The MS SQL Server json_query function, single argument variant.

NOTE: This helper only works correctly if you've installed the ParseJSONResultsPlugin. Otherwise the nested selections will be returned as JSON strings.

The plugin can be installed like this:

import { Kysely, MssqlDialect, ParseJSONResultsPlugin } from 'kysely'
import * as Tarn from 'tarn'
import * as Tedious from 'tedious'
import type { Database } from 'type-editor' // imaginary module

const db = new Kysely<Database>({
  dialect: new MssqlDialect({
    tarn: { options: { max: 10, min: 0 }, ...Tarn },
    tedious: {
      ...Tedious,
      connectionFactory: () => new Tedious.Connection({
        authentication: {
          options: { password: 'password', userName: 'sa' },
          type: 'default',
        },
        options: { database: 'test', port: 21433, trustServerCertificate: true },
        server: 'localhost',
      }),
    },
  }),
  plugins: [new ParseJSONResultsPlugin()]
})
Copy

Examples

import { jsonBuildObject } from 'kysely/helpers/mssql'

const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    jsonBuildObject({
      first: eb.ref('first_name'),
      last: eb.ref('last_name'),
      full: eb.fn('concat', ['first_name', eb.val(' '), 'last_name'])
    }).as('name')
  ])
  .execute()
Copy

The generated SQL (MS SQL Server):

select "id", json_query(
  '{"first":"'+"first_name"+',"last":"'+"last_name"+',"full":"'+concat("first_name", ' ', "last_name")+'"}'
) as "name"
from "person"
Copy