Json advanced support

[OlivierCavadenti]

I start #1337 1 month ago and after some iterations and pauses, I push a draft if anyone want to start a review, it's quite big.
JSON support is quite challenging, because all database have support of JSON, but in really different ways (syntax, cast, paths...)

Implemented :

• Json extraction of data.

one extraction way (json column, jsonpath, alias):

 const res = await knex
    .jsonExtract('descriptions', '$.short', 'shortDescription')
    .from('cities');

multiple extraction way (pretty powerful):

const res = await knex
    .jsonExtract([
      ['descriptions', '$.short', 'shortDescription'],
      ['population', '$.current', 'currentPop'],
      ['statistics', '$.roads.min', 'minRoads'],
      ['statistics', '$.hotYears[0]', 'firstHotYear'],
    ])
    .from('cities');

• Json manipulation of data

Json set, json insert, json remove (alias support)

  const res = await knex
    .jsonSet('population', '$.max', '999999999', 'maxUpdated')
    .from('cities');

const res = await knex
    .jsonInsert('population', '$.year2021', '747477', 'popIn2021Added')
    .from('cities');

const res = await knex
  .jsonRemove('population', '$.min', 'popMinRemoved')
  .from('cities');

All extraction + manipulation functions are made to be used like 'select' or by nested like :

const res = await knex
  .jsonExtract([
    [knex.jsonRemove('population', '$.min'), '$', 'withoutMin'],
    [knex.jsonRemove('population', '$.max'), '$', 'withoutMax'],
    [
      knex.jsonSet('population', '$.current', '1234'),
      '$',
      'currentModified',
    ],
  ])
  .from('cities');

• Wheres JSON

• whereJsonObject (and/or/not functions) : test if column is equals to json object.
• whereJsonPath (and/or functions) : test if column is equals to value of json path (work with text and with >, < and numeric ).
• whereJsonSupersetOf (and/or/not functions) : test if json column is superset of json (only postgres/mysql).
• whereJsonSubsetOf (and/or/not functions) : test if json column is subset of json (only postgres/mysql).

• JSON Join

It's possible to join tables by json value from json columns like :

 knex.select('*')
    .from('users')
    .join('contacts', (qb) => {
      qb.onJsonPathEquals(
        'users.infos',
        '$.name',
        'contacts.data',
        '$.infos.name'
      );
    })

• Support of JSON object in value of JSON functions, you can put json in strings always, but it's way clear to put json objects. Knex will detect it and stringify it.

[kibertoad]

@rijkvanzanten Any chance you guys could help test this with Oracle? I know you support it in Directus, so I assume you have working image on the ready :)

[OlivierCavadenti]

@rijkvanzanten Any chance you guys could help test this with Oracle? I know you support it in Directus, so I assume you have working image on the ready :)

I will update all tests + typings + documentation this week and I will able to run test in Oracle (I install all tools and have a new blazing fast computer \o/ ).

[intech]

@OlivierCavadenti when you complete the main work and there are only cockroach tests left, call me to this thread, I will add replacement functions for the dialect.

And I suggest adding a cockroach tag and using that so I can quickly find topics that need help with cockroach.

Thanks!

[OlivierCavadenti]

@OlivierCavadenti when you complete the main work and there are only cockroach tests left, call me to this thread, I will add replacement functions for the dialect.

Cool ! Yeah I will do that. You will be able to push on the branch (I create a branch on this repo but it's not really my goal... I will push later my other PR on my fork, like other users xD).

And I suggest adding a cockroach tag and using that so I can quickly find topics that need help with cockroach.

Really thanks for the suggestion, I will do that.

[rijkvanzanten]

@rijkvanzanten Any chance you guys could help test this with Oracle? I know you support it in Directus, so I assume you have working image on the ready :)

Totally! We've based our docker-compose setup on the one included in the knex tests, and altered it slightly to use a smaller image. Not sure how that affects the Knex test suite necessarily, but the image itself runs fine and allows for easy (local) debugging:

# In docker-compose.yaml
# ...

  oracle:
    image: quillbuilduser/oracle-18-xe-micro-sq
    ports:
      - 1521:1521
    environment:
      - OPATCH_JRE_MEMORY_OPTIONS=-Xms128m -Xmx256m -XX:PermSize=16m -XX:MaxPermSize=32m -Xss1m
      - ORACLE_ALLOW_REMOTE=true
    shm_size: '1gb' # more like smh-size ammirite 🥁

# ...

One thing to note: we've had a lot of trouble getting NodeJS to connect properly to OracleDB in a containerized environment though, due to Oracle's dependency on their instantclient package.. I'm also pretty sure this image will not run at all on newer ARM based machines (eg Macs with M1).

[kibertoad]

@OlivierCavadenti I am thinking about releasing 1.0.0 after this lands, we have plenty of good stuff lined up already. Do you feel like anything else should make the cut too?

[OlivierCavadenti]

@OlivierCavadenti I am thinking about releasing 1.0.0 after this lands, we have plenty of good stuff lined up already. Do you feel like anything else should make the cut too?

No, I will finish this and work to polish checks constraints (I also have castTo functions in the pipe) but I think is better to stabilize all the stuff. And Json Indexes will be in a second part I think

[OlivierCavadenti]

@OlivierCavadenti I am thinking about releasing 1.0.0 after this lands, we have plenty of good stuff lined up already. Do you feel like anything else should make the cut too?

I add all missing unit tests (finally, integration seems good) and update index.d.ts (I just need to add json manipulations functions, I am not familiar with complex TypeScript index.d.ts types so I will update later).

I made some tests on Oracle and unfortunaly I have ' ORA-40454: path expression not a literal' errors (json path is a classic string parameters... need to investigate) and other syntax errors for other functions (json_transform is introduced in Oracle 21c...).
So if I fix the path expression error, I assume column json type addition I made + some of where clauses can be used with Oracle. I need to install Oracle 21c to check Json transform functions, if we decide to add them with version restriction (available only with Oracle 21c+).

[OlivierCavadenti]

@intech can you check cockroach tests plz ?

[OlivierCavadenti]

@kibertoad I push typings so I think I am done with this first version. I will push documentation in 1 or 2 days.

[intech]

@intech can you check cockroach tests plz ?

Yes! How do we deal with the jsonb_path_query_first function that is not supported? I will have to duplicate the code again in order to replace this function with another in all requests.
Can we move it into a variable? Perhaps we will move all the functions into some kind of enumeration so that they can be redefined there?

[OlivierCavadenti]

@intech can you check cockroach tests plz ?

Yes! How do we deal with the jsonb_path_query_first function that is not supported? I will have to duplicate the code again in order to replace this function with another in all requests. Can we move it into a variable? Perhaps we will move all the functions into some kind of enumeration so that they can be redefined there?

I made a generic '_jsonExtract' function in querycompiler that take the name of json function to query and params (column, path, value,...). For example this._jsonExtract('jsonb_path_query', params) in PG.
It seems CockroachDB dont support JsonPath queries and only '->' and '->>' notations to query json ?
I can check what we can do to support that notation to provide a support for basic json querying.

Maybe specify jsonExtract and override _jsonExtract function in querycompiler that will transform a jsonPath from 'params.path' variable into '->' expression (like toPathForJson functions for RedShift and PG in index.js files). If we cant transform jsonPath into '->' notation, we will raise an error.

[OlivierCavadenti]

@intech I implement method to convert json path to json arrow notation to extract data from json in cockroach db.

I just need to fix join json path and it will be done.

[intech]

@OlivierCavadenti I was busy and did not have time to do it yet. The cockroach has a function json_extract_path (jsonb, string ...) → jsonb

[OlivierCavadenti]

@OlivierCavadenti I was busy and did not have time to do it yet. The cockroach has a function json_extract_path (jsonb, string ...) → jsonb

No problem, I am free today I will rework that today.

[kibertoad]

LGTM

[kibertoad]

Released in 1.0.1.