jsonb example

[joshxyzhimself]

Not really an issue, just surprised that it just works, might be helpful to some 💯

  await postgres`
    DROP TABLE IF EXISTS "tests_json" CASCADE;
  `;
  await postgres`
    CREATE TABLE IF NOT EXISTS "tests_json" (
      "id" serial PRIMARY KEY,
      "data" jsonb NOT NULL
    );
  `;
  const [inserted_row] = await postgres`
    INSERT INTO "tests_json" (
      "data"
    ) VALUES (
      ${JSON.stringify({ foo: 'bar', num: 1, bool: true, nil: null })}
    ) RETURNING *;
  `;
  console.log({ inserted_row });
  const [updated_row] = await postgres`
    UPDATE "tests_json"
    SET "data" = jsonb_set(data, '{num}', ${2}, true)
    WHERE id = ${inserted_row.id}
    RETURNING *; 
  `;
  console.log({ updated_row });

{
  inserted_row: { id: 1, data: { foo: 'bar', nil: null, num: 1, bool: true } }
}
{
  updated_row: { id: 1, data: { foo: 'bar', nil: null, num: 2, bool: true } }
}

---

v3 alt

• sql.json helper is missing in the docs #342

select ${ aJsObject }::jsonb
// works: returns parsed js object

[joshxyzhimself]

Edge case

With the following lines, notice the nested_path which could be any nested json path that we might want to change

    const [inserted_row] = await postgres`
      INSERT INTO "tests_json" (
        "data"
      ) VALUES (
        ${JSON.stringify({ foo: { bar: true } })}
      ) RETURNING *;
    `;
    console.log({ inserted_row });

+   const nested_path = 'bar';
    const [updated_row] = await postgres`
      UPDATE "tests_json"
+     SET "data" = jsonb_set(data, '{foo, ${nested_path}}', ${false}, true)
      WHERE id = ${inserted_row.id}
      RETURNING *; 
    `;
    console.log({ updated_row });

This gives the following error

PostgresError: could not determine data type of parameter $1

Which is related to #36 (comment)

Fix

    const nested_path = 'bar';
    const [updated_row] = await postgres`
      UPDATE "tests_json"
+     SET "data" = jsonb_set(data, array_append(ARRAY['foo'], ${nested_path}), ${false}, true)
      WHERE id = ${inserted_row.id}
      RETURNING *; 
    `;
    console.log({ updated_row });

Result

{
  updated_row: { id: 1, data: { foo: { bar: false } } }
}

[porsager]

If you're using json you should use sql.json, so in this case you'd put sql.json(nested_path) instead of simply nested_path.

You can also do JSON.stringify(nested_path), which is the same except that sql.json will pass the jsonb type explicitly where JSON.stringify will just be regular string, and postgres will do it's implicit casting.

[basaran]

This seems to be the correct formatting for jsonb_set and sql.json. The only issue with your package is that everything is so magical, it's sometimes hard to pinpoint what goes where.

const id = "AAAA";
const scummBarPirateCount = 14;
const trying = await sql`
    UPDATE melee
    SET island = jsonb_set(island, '{scummBarPirates}', ${sql.json(scummBarPirateCount)})
    WHERE id = ${id}`;

Thank you :)