Kysely•2y ago

Convert JSON array result into JS array

Hello, I am running an SQL query that returns data in this format since it is a PG JSON array:

{
  rows: [
    {
      array_agg: "{cat,dog,horse,camel,tiger}"
    }
  ]
}

{
  rows: [
    {
      array_agg: "{cat,dog,horse,camel,tiger}"
    }
  ]
}

How can I convert such an array in PG format to a string array in JS? Or better yet is there any way that kysely can do that already internally?

15 Replies

Unknown User•2y ago

Message Not Public

koskimas•2y ago

That seems like a stringified normal postgres array and not a JSON array. You can't parse that as JSON. Is the column type something like json[]? If so, remove the array. JSON can hold arrays.

bombillazoOP•2y ago

this is the query im running:

  const response = await sql<TablesGetReturns>`
  SELECT ARRAY_AGG(table_name)
  FROM information_schema.tables
  WHERE table_schema = 'public';`.execute(kysely);

  const response = await sql<TablesGetReturns>`
  SELECT ARRAY_AGG(table_name)
  FROM information_schema.tables
  WHERE table_schema = 'public';`.execute(kysely);

I want to get the data as an array of strings I did it as a raw query since Im not sure how to query the table information_schema.tables wiht teh builder even when I do it like this:

  const response = await kysely
    .selectFrom('information_schema.tables')
    .select(({ fn }) => [
      fn.agg<string[]>('array_agg', ['table_name']).as('tables'),
    ])
    .where('table_schema', '=', 'public')
    .execute();

  const response = await kysely
    .selectFrom('information_schema.tables')
    .select(({ fn }) => [
      fn.agg<string[]>('array_agg', ['table_name']).as('tables'),
    ])
    .where('table_schema', '=', 'public')
    .execute();

in that lower case I get type errors because the introspection does not pull information_schema.tables but I get he same result still comes out as a stringified postgres array... 😕 this expression:

 fn.agg<string[]>('array_agg', ['table_name']).as('tables'),

 fn.agg<string[]>('array_agg', ['table_name']).as('tables'),

returns this: tables: "{business,address,...}"

koskimas•2y ago

Which dialect are you using? array_agg doesn't create a JSON array. It creates a postgres array The problem is that the dialect you are using doesn't parse arrays

bombillazoOP•2y ago

im using postgres

koskimas•2y ago

Kysely never touches the output data. It returns what the dialect and the underlying DB driver returns But which dialect?

bombillazoOP•2y ago

hmmm

koskimas•2y ago

Like which dialect class

bombillazoOP•2y ago

let me confirm quick

koskimas•2y ago

how do you create the Kysely instance

bombillazoOP•2y ago

I'm in Deno so its a custom dialect I presume using the Deno postgres driver

koskimas•2y ago

Ok, so then I can't help you. Third party dialects do what they do

bombillazoOP•2y ago

ok gotcha, but that helps me at least understand where the issue stems from 🙂 thanks for the help, and I'm liking Kysely very much! UPDATE Using JSONB_AGG instead of ARRAY_AGG fixes the issue for me

Igal•2y ago

Hey 👋 We have tests in place proving that our core PostgresDialect works in Deno. the underlying node-postgres (pg) driver supports custom transformations of results by data type

Igal•2y ago

https://github.com/kysely-org/kysely/tree/master/test/deno

GitHub

kysely/test/deno at master · kysely-org/kysely

A type-safe typescript SQL query builder. Contribute to kysely-org/kysely development by creating an account on GitHub.

Gaming

Programming

Convert JSON array result into JS array

Did you find this page helpful?