new | ask | show | jobs
nchmy 14 hours ago  [ - ]

obviously the example is contrived, but it seems strange me that they are not storing the json in its own column - just extracting a single key from it and storing in a generated column. Why not do that in the app code if youre just going to discard the rest of the json?

Here's an example i saw yesterday from mariadb, which is improving its json support in its upcoming releases.

https://mariadb.com/docs/server/ha-and-performance/optimizat...

``` CREATE TABLE t1 (json_data JSON); INSERT INTO t1 VALUES('{"column1": 1234}'); INSERT INTO t1 ... ```

In order to do efficient queries over data in JSON, you can add a virtual column, and an index on that column:

``` ALTER TABLE t1 ADD COLUMN vcol1 INT AS (cast(json_value(json_data, '$.column1') AS INTEGER)), ADD INDEX(vcol1);

```

vidarh 13 hours ago  [ - ]

"body" is the "json in its own column" you're asking for. They are doing exactly what you're saying.

The point is exactly that it means you can selectively retrospectively add virtual columns, optionally backed with an index, as you decide which fields you need more structured access to.

The example you're giving is in principle the same as the "ALTER TABLE ... GENERATED ALWAYS AS ... VIRTUAL" example + a subsequent index in the Sqlite example.

inigyou 13 hours ago  [ - ]

Postgres does even better and it's available right now. No virtual column needed.

    CREATE TABLE t1 (data JSONB);
    INSERT INTO t1 VALUES ('{"column1":1234}');
    CREATE INDEX t1column1 ON t1(data->'column1');
    SELECT * FROM t1 WHERE data1->'column1' = '1234'; // not sure about data type
mathnode 12 hours ago  [ - ]

I would not call an index format which gets slower with growth "better". And that vacuum issue on JSONB scales with your data size.

inigyou 11 hours ago  [ - ]

What do you mean slower with growth? What vacuum issue?