`relationsJoins` enabled with `@db.Json` in Postgres makes the order of the JSON fields completely disappear

[semoal]

Bug description

We have a field called "layout" that is a JSON stored inside a template schema, we have noticed that our templates were crashing because out of order, after analysing what happened we have seen that in the database was in the correct order but after calling:

const collectionWithTemplates = await prisma.collection.findMany({ include: { template: true }})

The template object which contains a layout field that is a @db.JSON was not ordered/preserved the order correctly.

How to reproduce

Simply create an schema with a relation with json field, and fetch this field through a relation

Expected behavior

It should keep the order

Prisma information

Prisma schema

generator client {
  provider        = "prisma-client-js"
  output          = "../../../packages/prisma-client/client"
  previewFeatures = ["fullTextSearch", "tracing", "relationJoins", "nativeDistinct"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Collection {
  id String @id @default(uuid()) @db.Uuid
  templateId String             @db.Uuid
  template   Template @relation(fields: [templateId], references: [id], onDelete: Cascade)
}

model Template {
  id String @id @default(uuid()) @db.Uuid
  layout       Json           @db.Json
  collections  Collection[]
}

Code to check

  const template = await prisma.collection.findFirst({
    include: {
      template: true,
    },
  });

Environment & setup

• OS: Ubuntu/Mac
• Database: PostgreSQL
• Node.js version: Node 18

Prisma Version

5.7.1

[imwexpex]

We're facing the same issue which is critical for us.

The field in schema:

  roster     Json @db.Json

JS call:

await prisma.leaguer.league.findFirst({
      where: {
        uuid: '018ccfc5-7d85-78ba-807e-5690a8d140d4',
      },
      select: {
        settings: {
          select: {
            roster: true,
          },
        },
      },
    }),

Generated SQL Query:

SELECT
    "t1"."uuid",
    "t1"."settingsUuid",
    "League_settings"."__prisma_data__" AS "settings"
FROM
    "public"."League" AS "t1"
        LEFT JOIN LATERAL (
        SELECT
            COALESCE(
                    JSONB_AGG("__prisma_data__"),
                    '[]'
            ) AS "__prisma_data__"
        FROM
            (
                SELECT
                    "t4"."__prisma_data__"
                FROM
                    (
                        SELECT
                            JSONB_BUILD_OBJECT('roster', "t3"."roster") AS "__prisma_data__"
                        FROM
                            (
                                SELECT
                                    "t2".*
                                FROM
                                    "public"."Settings" AS "t2"
                                WHERE
                                    "t1"."settingsUuid" = "t2"."leagueUuid"
                                /* root select */
                            ) AS "t3"
                        /* inner select */
                    ) AS "t4"
                /* middle select */
            ) AS "t5"
        /* outer select */
        ) AS "League_settings" ON true
WHERE
    "t1"."uuid" = '018ccfc5-7d85-78ba-807e-5690a8d140d4'

The issue caused by:

JSONB_AGG
JSONB_BUILD_OBJECT

The engine does not respect the JSON type and always uses jsonB functions which breaks ordering
This was caused by the following workaround for another issue.

[renatocron]

Is this changing only the order of the keys, right? not the order of arrays, for example.
If this is the case, Prisma (or rather, the JSONB) will remove any duplicate field, but that would happen anyway when loading then in a plain JS object

Relying on the order of keys inside a JSON is a little sketchy, I feel sorry for you.

[imwexpex]

Is this changing only the order of the keys, right? not the order of arrays, for example. If this is the case, Prisma (or rather, the JSONB) will remove any duplicate field, but that would happen anyway when loading then in a plain JS object

Relying on the order of keys inside a JSON is a little sketchy, I feel sorry for you.

What do you mean by order of arrays, nested arrays, like ex: [[{key: "arr_1"}], [{key: "arr_2"}]]?

---

Relying on the order of keys inside a JSON is a little sketchy, I feel sorry for you.

In some cases, it's needed. The only option here is an additional round-trip for restoring original keys ordering in JS, for example using Zod schema

[renatocron]

insert into "Template" (id, layout) select '8ba4599b-1116-4a14-bebc-1bfc0d14169a', '[1,2,3,4]';
insert into "Collection" (id, "templateId") select '00000000-1116-4a14-bebc-1bfc0d14169a', '8ba4599b-1116-4a14-bebc-1bfc0d14169a';

Prisma runs:

SELECT "t1"."id", "t1"."templateId", "Collection_template"."__prisma_data__" AS "template" FROM "public"."Collection" AS "t1" LEFT JOIN LATERAL (SELECT COALESCE(JSONB_AGG("__prisma_data__"), '[]') AS "__prisma_data__" FROM (SELECT "t4"."__prisma_data__" FROM (SELECT JSONB_BUILD_OBJECT('id', "t3"."id", 'layout', "t3"."layout") AS "__prisma_data__" FROM (SELECT "t2".* FROM "public"."Template" AS "t2" WHERE "t1"."templateId" = "t2"."id" /* root select */) AS "t3" /* inner select */) AS "t4" /* middle select */) AS "t5" /* outer select */) AS "Collection_template" ON true LIMIT $1 [1] took 1ms

Order is preserved, as data is saved inside an array:

{
  id: '00000000-1116-4a14-bebc-1bfc0d14169a',
  templateId: '8ba4599b-1116-4a14-bebc-1bfc0d14169a',
  template: {
    id: '8ba4599b-1116-4a14-bebc-1bfc0d14169a',
    layout: [ 1, 2, 3, 4 ]
  }
}

If we update the record to the typical key/value:

update "Template" set layout = '{"first_key": "1", "second_key":"2", "...":"value"}'；

Prisma runs the same query:

SELECT "t1"."id", "t1"."templateId", "Collection_template"."__prisma_data__" AS "template" FROM "public"."Collection" AS "t1" LEFT JOIN LATERAL (SELECT COALESCE(JSONB_AGG("__prisma_data__"), '[]') AS "__prisma_data__" FROM (SELECT "t4"."__prisma_data__" FROM (SELECT JSONB_BUILD_OBJECT('id', "t3"."id", 'layout', "t3"."layout") AS "__prisma_data__" FROM (SELECT "t2".* FROM "public"."Template" AS "t2" WHERE "t1"."templateId" = "t2"."id" /* root select */) AS "t3" /* inner select */) AS "t4" /* middle select */) AS "t5" /* outer select */) AS "Collection_template" ON true LIMIT $1 [1] took 0ms

{
  id: '00000000-1116-4a14-bebc-1bfc0d14169a',
  templateId: '8ba4599b-1116-4a14-bebc-1bfc0d14169a',
  template: {
    id: '8ba4599b-1116-4a14-bebc-1bfc0d14169a',
    layout: { '...': 'value', first_key: '1', second_key: '2' }
  }
}

But the order is not preserved.

Running the query on the database, the order is already destroyed by Postgres, as you said before, because of the JSONB:

-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------
id         | 00000000-1116-4a14-bebc-1bfc0d14169a
templateId | 8ba4599b-1116-4a14-bebc-1bfc0d14169a
template   | [{"id": "8ba4599b-1116-4a14-bebc-1bfc0d14169a", "layout": {"...": "value", "first_key": "1", "second_key": "2"}}]

JSON fields are stored as a string, but JSONB is stored as binary and is a bit optimized as well.

If I change the query to use JSON instead of JSONB, the order of the keys is preserved:

SELECT "t1"."id", "t1"."templateId", "Collection_template"."__prisma_data__" AS "template" FROM "public"."Collection" AS "t1" LEFT JOIN LATERAL (SELECT COALESCE(JSON_AGG("__prisma_data__"), '[]') AS "__prisma_data__" FROM (SELECT "t4"."__prisma_data__" FROM (SELECT JSON_BUILD_OBJECT('id', "t3"."id", 'layout', "t3"."layout") AS "__prisma_data__" FROM (SELECT "t2".* FROM "public"."Template" AS "t2" WHERE "t1"."templateId" = "t2"."id" /* root select */) AS "t3" /* inner select */) AS "t4" /* middle select */) AS "t5" /* outer select */) AS "Collection_template" ON true LIMIT 1;

id         | 00000000-1116-4a14-bebc-1bfc0d14169a
templateId | 8ba4599b-1116-4a14-bebc-1bfc0d14169a
template   | [{"id" : "8ba4599b-1116-4a14-bebc-1bfc0d14169a", "layout" : {"first_key": "1", "second_key":"2", "...":"value"}}]

In some cases, it's needed. The only option here is an additional round-trip for restoring original keys ordering in JS, for example using Zod schema

I'm still out of creativity in how the order of keys were persisted/restored into a dictionary should influence how the data is used. This behavior that other software is depending on is extremely fragile. That's why I said I was sorry; I'm not trying to be rude or anything.

Some languages even force that between each program start the hashes be randomized to prevent some hash-collision attacks, so it's kind of impossible to have the order preserved when reading JSON into an object except if implementing a custom reader.

According to the JSON specification (RFC 8259), the order of keys within an object is not guaranteed to be preserved. JSON is designed as a data interchange format, and parsers are free to rearrange the order of keys during processing.

To fix this, if the app is in him domain, he should use arrays or other data structures where order is explicitly maintained (per RFC, like any array in any software)

If the change in the struct is not possible, include an additional field to store the order explicitly if necessary

[Weakky]

Hey @imwexpex,

In some cases, it's needed.

Would you mind clarifying what is your specific use case?

Thank you all for chiming in.

[imwexpex]

Hey @imwexpex,

In some cases, it's needed.

Would you mind clarifying what is your specific use case?

Thank you all for chiming in.

We're storing configuration for entry in JSON, where key ordering is valuable and directly affects business logic.
For example, draft order {QB: 1, WR: 2} => This means that 1 QB must be picked first, and 2 WRs second