Error on subSelect when picking just one value

[leosuncin]

Given the following schema in SQL Server

CREATE TABLE categories (
  category_id bigint IDENTITY (1, 1) PRIMARY KEY,
  category_name varchar(255) NOT NULL
)

CREATE TABLE brands (
  brand_id bigint IDENTITY (1, 1) PRIMARY KEY,
  brand_name varchar(255) NOT NULL
)

CREATE TABLE products (
  product_id bigint IDENTITY (1, 1) PRIMARY KEY,
  product_name varchar(255) NOT NULL,
  model_year smallint NOT NULL,
  list_price DECIMAL(10, 2) NOT NULL,
  brand_id bigint NOT NULL,
  category_id bigint NOT NULL,
  FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (brand_id) REFERENCES brands(brand_id) ON DELETE CASCADE ON UPDATE CASCADE
)

Test data

SET IDENTITY_INSERT brands ON;

INSERT INTO brands(brand_id, brand_name) VALUES (1, 'Electra');

SET IDENTITY_INSERT brands OFF;

SET IDENTITY_INSERT categories ON;

INSERT INTO categories(category_id, category_name) VALUES (1, 'Children Bicycles');

SET IDENTITY_INSERT production.categories OFF;

SET IDENTITY_INSERT production.products ON;

INSERT INTO production.products(product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (1, 'Electra Cruiser 1 (24-Inch) - 2016', 1, 1, 2016, 269.99);

SET IDENTITY_INSERT production.products OFF;

I want to select a product with brand and category as JSON columns, so I build the following query

const brand = new Brand();
const category = new Category();
const product = new Product().as('product');
const id = 1n;

const singleProductWithCategoryAndBrand = await connection
      .selectFrom(product)
      .select({
        id: product.id,
        name: product.name,
        modelYear: product.modelYear,
        listPrice: product.listPrice,
        category: connection
          .subSelectUsing(product)
          .from(category)
          .select({ id: category.id, name: category.name })
          .where(category.id.equals(product.categoryId))
          .customizeQuery({
            beforeQuery: connection.rawFragment`JSON_QUERY((`,
            afterQuery: connection.rawFragment`), '$[0]')`
          })
          .forUseAsInlineAggregatedArrayValue(),
        brand: connection
          .subSelectUsing(product)
          .from(brand)
          .select({ id: brand.id, name: brand.name })
          .where(brand.id.equals(product.brandId))
          .customizeQuery({
            beforeQuery: connection.rawFragment`JSON_QUERY((`,
            afterQuery: connection.rawFragment`), '$[0]')`
          })
          .forUseAsInlineAggregatedArrayValue(),
      })
      .where(product.id.equals(id))
      .executeSelectOne();

And produce the following query which is valid, I tested it directly in SQL Server

select
    product.product_id as id,
    product.product_name as name,
    product.model_year as modelYear,
    product.list_price as listPrice,
    (
        JSON_QUERY(
            (
                select
                    category_id as id,
                    category_name as name
                from
                    categories
                where
                    category_id = product.category_id
                for json path
            ),
            '$[0]'
        )
    ) as category,
    (
        JSON_QUERY(
            (
                select
                    brand_id as id,
                    brand_name as name
                from
                    brands
                where
                    brand_id = product.brand_id
                for json path
            ),
            '$[0]'
        )
    ) as brand
from
    production.products as product
where
    product.product_id = @0

But when I run the code I got an error

Error: Invalid JSON string coming from the database for the column `category`. An array were expected

Because forUseAsInlineAggregatedArrayValue expects an array but for my case it only produces one result in the subquery, so maybe you can add a method called forUseAsInlineValue for the cases when only a single result is expected

[juanluispaz]

Hi,

It here any reason why are you doing a subquery instead of joins? Usually a join will be referred over a subquery.

If I use join I can rewrote your query as:

const brand = new Brand();
const category = new Category();
const product = new Product().as('product');
const id = 1n;

const singleProductWithCategoryAndBrand = await connection
      .selectFrom(product)
      .join(category).on(category.id.equals(product.categoryId))
      .join(brand).on(brand.id.equals(product.brandId))
      .select({
        id: product.id,
        name: product.name,
        modelYear: product.modelYear,
        listPrice: product.listPrice,
        category: { 
              id: category.id, 
              name: category.name 
        },
        brand: { 
              id: brand.id, 
              name: brand.name 
        }
      })
      .where(product.id.equals(id))
      .executeSelectOne();

This will do what you want in a more readable way. Can you explain yourself a little bit.

Note: In case your relation were optional, I will use a left join.

Some documentation: Complex projections, Inner objects and joins, Inner objects and left joins

Let me know your feedback.

[leosuncin]

I'm currently using mssql to connect and query the DB, so I was trying to replicate the queries that I already have.

The one where I use FOR JSON AUTO and JSON_QUERY is to get a resultset ready to send as a JSON object, so with mssql if I use joins then I'll have to transform the columns of the resultset into a JSON object before send it.

[juanluispaz]

But, be aware, forUseAsInlineAggregatedArrayValue will not return the JSON string representation; instead, a real object will be projected in JavaScript (yes, it uses for json path in SQL Server to perform it, but you never will get the JSON string).

Sending the straightforward JSON representation of the output will be very limiting for your backend, and if that is the case, why do not just send the whole result of the query as JSON serialized in the DB?

I'm not sure why you want to hide those results to your backend but still keep some parts visible.

If you still need a JSON string representation of your query, you can use a Sql Fragment to change the output (the result of the fragment have a different type than the subquery type), something like this:

const brand = new Brand();
const category = new Category();
const product = new Product().as('product');
const id = 1n;

const singleProductWithCategoryAndBrand = await connection
      .selectFrom(product)
      .select({
        id: product.id,
        name: product.name,
        modelYear: product.modelYear,
        listPrice: product.listPrice,
        category: connection.fragmentWithType('string', 'required').sql`JSON_QUERY((${
          connection
          .subSelectUsing(product)
          .from(category)
          .select({ id: category.id, name: category.name })
          .where(category.id.equals(product.categoryId))
          .forUseAsInlineAggregatedArrayValue(),
        }), '$[0]')`
        brand: connection.fragmentWithType('string', 'required').sql`JSON_QUERY((${
          connection.
          .subSelectUsing(product)
          .from(brand)
          .select({ id: brand.id, name: brand.name })
          .where(brand.id.equals(product.brandId))
          .forUseAsInlineAggregatedArrayValue(),
        }, '$[0]')`
      })
      .where(product.id.equals(id))
      .executeSelectOne();

I've released ts-sql-query 1.56.0, fixing an issue that will not allow you to use the subquery in a sql fragment.

Let me know what you think.