Error: too many arguments on function JSON_OBJECT - 🐛 Bug Report — Runtime APIs

[lifeiscontent]

trying to run the following with D1

produces the following error:

[cause]: Error: too many arguments on function JSON_OBJECT at offset 2909
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.first (cloudflare-internal:d1-api:159:35) {
    [cause]: undefined

WITH RecipeBase AS (
	SELECT
		r.id,
		r.name,
		r.date_published,
		r.description,
		r.recipe_cuisine,
		r.prep_time,
		r.cook_time,
		r.total_time,
		r.recipe_yield,
		r.recipe_category,
		u.name AS author_name,
		n.calories
	FROM
		recipes r
		LEFT JOIN users u ON r.author_id = u.id
		LEFT JOIN nutrition_information n ON r.nutrition_id = n.id
	WHERE
		r.id = 1
),
RecipeImages AS (
	SELECT
		ri.recipe_id,
		JSON_GROUP_ARRAY(ri.image_url) AS images
	FROM
		recipe_images ri
	WHERE
		ri.recipe_id = 1
	GROUP BY
		ri.recipe_id
),
RecipeKeywords AS (
	SELECT
		rk.recipe_id,
		JSON_GROUP_ARRAY(k.keyword) AS keywords
	FROM
		recipe_keywords rk
		JOIN keywords k ON rk.keyword_id = k.id
	WHERE
		rk.recipe_id = 1
	GROUP BY
		rk.recipe_id
),
RecipeIngredients AS (
	SELECT
		ri.recipe_id,
		JSON_GROUP_ARRAY(ri.ingredient) AS ingredients
	FROM
		recipe_ingredients ri
	WHERE
		ri.recipe_id = 1
	GROUP BY
		ri.recipe_id
),
HowToSteps AS (
	SELECT
		ss.recipe_instruction_section_id,
		JSON_GROUP_ARRAY(
			JSON_OBJECT(
				'@type', 'HowToStep', 'name', st.name,
				'text', st.text, 'image', st.image
			)
		) AS steps
	FROM
		recipe_instruction_section_steps ss
		JOIN recipe_instruction_steps st ON ss.recipe_instruction_step_id = st.id
	GROUP BY
		ss.recipe_instruction_section_id
),
RecipeInstructions AS (
	SELECT
		ri.recipe_id,
		JSON_GROUP_ARRAY(
			CASE WHEN ri.instruction_type = 'text' THEN ri.text WHEN ri.instruction_type = 'recipe_instruction_step' THEN JSON_OBJECT(
				'@type', 'HowToStep', 'name', s.name,
				'text', s.text, 'image', s.image
			) WHEN ri.instruction_type = 'recipe_instruction_section' THEN JSON_OBJECT(
				'@type',
				'HowToSection',
				'name',
				sec.name,
				'itemListElement',
				COALESCE(
					hts.steps,
					JSON_ARRAY()
				)
			) END
		) AS instructions
	FROM
		recipe_instructions ri
		LEFT JOIN recipe_instruction_steps s ON ri.instruction_id = s.id
		LEFT JOIN recipe_instruction_sections sec ON ri.instruction_id = sec.id
		LEFT JOIN HowToSteps hts ON sec.id = hts.recipe_instruction_section_id
	WHERE
		ri.recipe_id = 1
	GROUP BY
		ri.recipe_id
),
AggregateRatings AS (
	SELECT
		rr.recipe_id,
		AVG(rr.rating_value) AS rating_value,
		COUNT(rr.rating_value) AS rating_count
	FROM
		recipe_ratings rr
	WHERE
		rr.recipe_id = 1
	GROUP BY
		rr.recipe_id
)
SELECT
	JSON_OBJECT(
		'@context',
		'https://schema.org/',
		'@type',
		'Recipe',
		'name',
		rb.name,
		'image',
		COALESCE(
			RecipeImages.images,
			JSON_ARRAY()
		),
		'author',
		CASE WHEN rb.author_name IS NOT NULL THEN JSON_OBJECT(
			'@type', 'Person', 'name', rb.author_name
		) ELSE NULL END,
		'datePublished',
		rb.date_published,
		'description',
		rb.description,
		'recipeCuisine',
		rb.recipe_cuisine,
		'prepTime',
		rb.prep_time,
		'cookTime',
		rb.cook_time,
		'totalTime',
		rb.total_time,
		'keywords',
		COALESCE(
			RecipeKeywords.keywords,
			JSON_ARRAY()
		),
		'recipeYield',
		rb.recipe_yield,
		'recipeCategory',
		rb.recipe_category,
		'nutrition',
		CASE WHEN rb.calories IS NOT NULL THEN JSON_OBJECT(
			'@type', 'NutritionInformation',
			'calories', rb.calories
		) ELSE NULL END,
		'aggregateRating',
		CASE WHEN ar.rating_value IS NOT NULL THEN JSON_OBJECT(
			'@type', 'AggregateRating', 'ratingValue',
			ar.rating_value, 'ratingCount',
			ar.rating_count
		) ELSE NULL END,
		'recipeIngredient',
		COALESCE(
			RecipeIngredients.ingredients,
			JSON_ARRAY()
		),
		'recipeInstructions',
		COALESCE(
			JSON(
				RecipeInstructions.instructions
			),
			JSON_ARRAY()
		)
	) AS recipe
FROM
	RecipeBase rb
	LEFT JOIN RecipeImages ON rb.id = RecipeImages.recipe_id
	LEFT JOIN RecipeKeywords ON rb.id = RecipeKeywords.recipe_id
	LEFT JOIN RecipeIngredients ON rb.id = RecipeIngredients.recipe_id
	LEFT JOIN RecipeInstructions ON rb.id = RecipeInstructions.recipe_id
	LEFT JOIN AggregateRatings ar ON rb.id = ar.recipe_id;

here's the schema:

-- Table to store users
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

-- Table to store nutrition information
CREATE TABLE nutrition_information (id INTEGER PRIMARY KEY, calories TEXT NOT NULL);

-- Table to store individual ratings
CREATE TABLE recipe_ratings (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER,
  user_id INTEGER,
  rating_value INTEGER NOT NULL,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id),
  FOREIGN KEY (user_id) REFERENCES users (id)
);

-- Table to store recipe instruction steps
CREATE TABLE recipe_instruction_steps (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  text TEXT NOT NULL,
  image TEXT NOT NULL
);

-- Table to store recipe instruction sections
CREATE TABLE recipe_instruction_sections (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

-- Junction table for recipe instruction sections and steps (many-to-many relationship)
CREATE TABLE recipe_instruction_section_steps (
  recipe_instruction_section_id INTEGER,
  recipe_instruction_step_id INTEGER,
  FOREIGN KEY (recipe_instruction_section_id) REFERENCES recipe_instruction_sections (id),
  FOREIGN KEY (recipe_instruction_step_id) REFERENCES recipe_instruction_steps (id),
  PRIMARY KEY (
    recipe_instruction_section_id,
    recipe_instruction_step_id
  )
);

-- Table to store recipes
CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  date_published TEXT NOT NULL,
  description TEXT NOT NULL,
  recipe_cuisine TEXT NOT NULL,
  prep_time TEXT NOT NULL,
  cook_time TEXT NOT NULL,
  total_time TEXT NOT NULL,
  recipe_yield TEXT NOT NULL,
  recipe_category TEXT NOT NULL,
  author_id INTEGER,
  nutrition_id INTEGER,
  FOREIGN KEY (author_id) REFERENCES users (id),
  FOREIGN KEY (nutrition_id) REFERENCES nutrition_information (id)
);

-- Table to store recipe images (one-to-many relationship)
CREATE TABLE recipe_images (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER,
  image_url TEXT NOT NULL,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id)
);

-- Table to store keywords
CREATE TABLE keywords (id INTEGER PRIMARY KEY, keyword TEXT NOT NULL);

-- Junction table to link recipes to keywords (many-to-many relationship)
CREATE TABLE recipe_keywords (
  recipe_id INTEGER,
  keyword_id INTEGER,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id),
  FOREIGN KEY (keyword_id) REFERENCES keywords (id),
  PRIMARY KEY (recipe_id, keyword_id)
);

-- Table to store recipe ingredients (one-to-many relationship)
CREATE TABLE recipe_ingredients (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER,
  ingredient TEXT NOT NULL,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id)
);

-- Table to store recipe instructions (one-to-many relationship)
CREATE TABLE recipe_instructions (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER,
  instruction_type TEXT NOT NULL,
  instruction_id INTEGER,
  text TEXT,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id)
);

-- Insert sample data into related tables first
INSERT INTO
  users (id, name)
VALUES
  (1, 'John Doe');

INSERT INTO
  nutrition_information (id, calories)
VALUES
  (1, '200 calories');

INSERT INTO
  recipes (
    id,
    name,
    date_published,
    description,
    recipe_cuisine,
    prep_time,
    cook_time,
    total_time,
    recipe_yield,
    recipe_category,
    author_id,
    nutrition_id
  )
VALUES
  (
    1,
    'Sample Recipe',
    '2024-07-18',
    'A delicious sample recipe.',
    'International',
    'PT20M',
    'PT30M',
    'PT50M',
    '4 servings',
    'Dessert',
    1,
    1
  );

INSERT INTO
  recipe_instruction_steps (id, name, text, image)
VALUES
  (1, 'Step 1', 'Mix ingredients.', 'image1.jpg');

INSERT INTO
  recipe_instruction_sections (id, name)
VALUES
  (1, 'Section 1');

-- Insert records into recipe_instructions
INSERT INTO
  recipe_instructions (recipe_id, instruction_type, instruction_id, text)
VALUES
  (1, 'text', NULL, 'Preheat oven to 350 degrees.'),
  (1, 'recipe_instruction_step', 1, NULL),
  (1, 'recipe_instruction_section', 1, NULL);

-- Insert sample rating
INSERT INTO
  recipe_ratings (recipe_id, user_id, rating_value)
VALUES
  (1, 1, 5);

[kentonv]

This is almost certainly an error coming directly from sqlite. That is, you'd get the same error when running sqlite locally, independent of Workers. There probably isn't anything we can do about this, you would need to file an issue upstream with sqlite.

[lifeiscontent]

@kentonv, nope, it works fine in SQLite otherwise I wouldn't of reported it. I literally used the .db file that is produced from D1 when using cloudflare proxy locally, and opening that in https://www.sqlitepro.com/ and running the same query produces no Error.

[kentonv]

Oh, come to think of it, I suppose it's probably from this:

workerd/src/workerd/util/sqlite.c++

Line 812 in d46550a

sqlite3_limit(db, SQLITE_LIMIT_FUNCTION_ARG, 32);

Your invocation has 36 args, the limit is 32.

https://www.sqlite.org/security.html actually recommends a limit of 8, but we increased it to 32 at some point.

Is it possible to rephrase this code in a way that doesn't require so many args?

We could maybe increase this further but I'm hesitant to diverge from the security recommendations too much.

[lifeiscontent]

@kentonv that seems like a solid guess,

here's a few things I've tried. 1. I removed the top level JSON_OBJECT function invocation and just do a simple SELECT ... as field on each field, the issue is the D1 adapter than only returns a Record<string, string> so I then have to individually parse each value with a JSON.parse, not great, but workable..

anyway, I'm open to suggestions, just trying to find a nice way to do complex selects like this without too much massaging of the data.

also using the JSON functions in SQL lite is a lot faster than manual manipulation in JS

would you guys be willing to bump to 64?

[lifeiscontent]

@kentonv bump

[lifeiscontent]

Thank you @kentonv 🎉