PostgreSQL JSON Type

[karai17]

PostgreSQL has a JSON type that I think would be very handy to have exposed in Lapis. One major use case I can think of would be the ability to cut down on junction tables by inserting data directly into a main table.

http://www.postgresqltutorial.com/postgresql-json/

Without JSON

Events

id	Name
1	Main

Divisions

id	Name	Age
1	Small	10
2	Medium	12
3	Large	14
4	Extra	16

EventDivisions

id	event_id	division_id	Price
1	1	1	99.99
2	1	2	149.99
3	1	4	199.99

With JSON

Events

id	Name	Price
1	Main	[ { "id" : 1, "price" : 99.99 }, { "id" : 2, "price" : 149.99 }, { "id" : 4, "price" : 199.99 } ]

Divisions

id	Name	Age
1	Small	10
2	Medium	12
3	Large	14
4	Extra	16

[turbo]

The driver already supports it: https://github.com/leafo/pgmoon/issues/15

[karai17]

That's handy~ But it is not exposed to Lapis yet, is it? I couldn't see anything in the docs.

[turbo]

Haven't tried it yet, but https://github.com/leafo/lapis/blob/master/lapis/db/postgres.moon and https://github.com/leafo/pgmoon/blob/master/pgmoon/json.moon are good starting points for an investigation.

Looks like your proposal is more akin to something like massive.js or Marten, where PG is accessed as a document database. If you just want JSON columns that should be easy to do with the driver support above.

[karai17]

PG has JSON columns and you can access the JSON within them using -> and ->> syntax in PG's SQL. It doesn't look like Lapis' model supports that directly right now so I think I'll just have to construct my own select statments via db.select if I want to interact with the JSON directly.