JSON type columns are returned as string

[dsolay]

trafficstars

Bug report

Required System information

• Node.js version: 18.17.1
• YARN version: 1.22.19
• Strapi version: 4.13.6
• Database: Postgres 15.3
• Operating system: ArchLinux x64, Kernel 6.4.12-arch1-1
• Is your project Javascript or Typescript: Typescript

Describe the bug

I updated to the latest version of strapi and realized that several of my endpoints were returning json data as strings instead of objects

Doing a little research I noticed this PR where the automatic conversion of JSONB type columns is disabled

I know I can perform a JSON.parse(result) on each of my endpoints but there are too many of them and I think this should be marked as a breaking change in the release notes

Steps to reproduce the behavior

1. Start strapi console
2. Exec this code

let result = await strapi.db.connection.raw("SELECT jsonb_build_object('key', 1) AS jsonb_column")
result.rows

Expected behavior

The result of the previous query should be an object instead of a string

• Actual result

[ { jsonb_column: '{"key": 1}' } ]

• Expected result

[ { jsonb_column: { key: 1 } } ]

[AnkurDhattarwal]

sir can you please assign this issue to me :)

[avdhendra]

can you assign me this issue

[gentakojima]

I've applied PR #18215 in my local installation and it fixes the issue for me. I completely broke my Gatsby app when updated Strapi from 4.12.x to 4.14.x.

[Boegie19]

@Feranchz since you self assigned yourself to this issue mind filling out the labels since I don't know what severity you want.

[rishabh1S]

Hey, anyone up who can review my PR #18215, which may close this issue.

[Boegie19]

@rishabh1S I assume it has been done for a reason so we need to wait for @Feranchz Who assigned himself to the issue and created the problem. so he would need to review your solution.

[rishabh1S]

@Boegie19 Sure thing man!

[Feranchz]

Hey @dsolay, thank you so much for reporting this and sorry for the late response.

I would appreciate if you can give me more context about your application and when is happening this error to you. I did try and I'm always receiving an object when fields are JSON values. The reason why Postgres' automatic conversion of JSONB fields is disabled is because we are parsing json fields by ourself here (In the PR you mentioned there is a more clear explanation about this). So if we enabled the automatic conversion you are parsing the field two times.

[Boegie19]

Hey @dsolay, thank you so much for reporting this and sorry for the late response.

I would appreciate if you can give me more context about your application and when is happening this error to you. I did try and I'm always receiving an object when fields are JSON values. The reason why Postgres' automatic conversion of JSONB fields is disabled is because we are parsing json fields by ourself here (In the PR you mentioned there is a more clear explanation about this). So if we enabled the automatic conversion you are parsing the field two times.

Looking at the initial issue report he is using a raw db connection and that is what we changed

[dsolay]

Apologies for the delayed response. As mentioned by @Boegie19, we utilize raw queries in our project.

Additional context: We have a dashboard that displays various metrics, such as orders by period, revenue, shipments, drayage by day, etc. To avoid making separate entity service calls for each metric, I constructed a single query and am utilizing the knex instance provided by strapi.db.connection to execute a raw query.

Before that pull request, everything worked fine

[bozz]

I just wanted to mention that this issue also came up with a previous release (v4.6.2): https://github.com/strapi/strapi/issues/16073

This also effects our app, as we use the raw knex instance for doing some queries.

[bozz]

Any updates on this and how this will be handled in the future? Can the knex instance be used directly in Strapi with the standard knex behaviour (i.e. JSON columns are returned as parsed JSON)?

[talhayoun]

Is it going to be solved anytime soon?

[dimhold]

Hi Strapi Team,

Thank you for your project.

I encountered the same issue while developing my strapi plugin. I'm using the pg-boss library to schedule jobs, which relies on the pg library to retrieve job data. Since jsonb data comes as a string, it breaks the pg-boss logic and causes the jobs to fail. I applied #18215 in my local installation, and it fixed the issue for me as well.

As a workaround in my plugin's bootstrap.ts, I did this: */my-plugin/server/bootstrap.ts:

function fixStrapiJsonbParser(strapi: any) {
  strapi.db.connection.client.driver.types.setTypeParser(
    strapi.db.connection.client.driver.types.builtins.JSONB,
    "text",
    (v) => {
      try {
        return JSON.parse(v);
      } catch (error) {
        return v;
      }
    }
  );
}

export default async ({ strapi }: { strapi: Strapi }) => {
   // Temporary fix for pg JSONB parsing (see https://github.com/strapi/strapi/issues/18073)
  fixStrapiJsonbParser(strapi);
};