Querying within JSON db fields via Graphql

1

8base is using a MySQL or a Postgres database behind the scenes? I ask because I’m wondering if the json data type I see in the data modeler stores json as text or if there is Postgres jsonb binary storage happening in the background?

I’m wondering about this because I’m wondering if there are inside-the-json querying options via 8base’s Graphql API like it seems Hasura has and wondering about the querying performance of doing this (I understand jsonb query performance is significantly faster.)

Use case: traditional table objects (products, people etc) with known fields but also with additional schema-less (& possibly arbitrary data depth) properties (via json). And to be able to query: Give me all products where {“Connector Type”: “USB”}.

And if it’s not postgres jsonb behind the scenes, two questions:

  1. I could still query data-within-the-json with graphql via 8base’s funky SQL-statements-as-Graphql-queries GroupBy functionality?

  2. Or should I just not even consider querying within json here because it will be a complete performance dog?

Thanks much.

2

Hello hello

8base is using MySQL behind the scenes: https://docs.8base.com/docs/8base-console/platform-tools/data-builder#tables and storing the JSON as an Auroa MySQL JSON type which has its own optimizations: https://dev.mysql.com/doc/refman/5.7/en/json.html.

For the example you provided, it would be advised to use a Table or Switch[Custom]. If your goal is to query by and manipulate expected field values on records, storing such data without known keys and at arbitrary depths doesn’t seem like a good strategy.

I’d recommend making a table called ConnectorTypes if that is something you’re going to be managing and updating actively, or adding a connectorTypes Switch field to your Products table with the Type custom so that they can easily be set.

We’re going to add querying support of our JSON fields in the future, though right now encourage people it’s best to use the system as advised.

3

Hello @sebastian.scholl if I could vote up features I’d vote for this; adding query support for JSON fields would be very welcome.

:slight_smile:

1 Like