How to database insert JSON?

    "name": "George",
    "country": "NL"

How can I literally Database Insert such JSON without actually going through the traditional Database Insert dialog and selecting the fields? It’s not feasible to manually build the Database Insert as the columns may vary over time and I’d rather just throw in the JSON representation. We’re talking about a lot of columns here (+50) and growing, so you can guess it’s not going to be easy to deal with changing Database Inserts to add/edit new fields in the middle of the already existing 50 columns

It seems it’s possible to do so for a subtable, but what about for a main table?

If I get what you are saying, you can insert a JSON object into a single column in the db like so:

In this example, listing_data is a complex nested json object.

I then use generated columns in mariadb for the critical pieces of data for searching, indexing, etc.

When you pull the data out, use .parseJSON() to return to an object.

Hi Ken,

Thanks for chiming in.

Following your example, I actually want to insert JSON like:

    "listing_data": (...),
    "listing_data_updated_at": (...)

It seems since Wappler 4.8 they’ve introduced this ability for subtables, but it’s not possible to do so on the main table as far as I see

Sorry, haven’t tried anything like that…this dataset stays relative stable, so I haven’t faced the challenge of constantly changing schema. Still, generated columns might be easier for you than the standard stuff if there is no back door for you.

We have a medical system that stores patient data in PostGres - there’s hundreds of fields in each category and they vary from patient to patient dependent on the forms they complete. I use JSON columns for each category within the patient data and that JSON is comprised of both individual fields and embedded JSON arrays.

We do pick some fields out occasionally for use in indexing or otherwise.

We also include many of these fields for search / indexing by adding them to search tables and full text search using gin/gist columms and tsvectors. We had been using elastic search but the gin/gist capability is equally performant on 5M+ records.

Here’s some table and insert examples …

We couldn’t easily manage the sheers number of columns without JSONB.

This looks cool stuff, thanks for sharing the insight!

It seems you’re doing something similar to Ken’s - I’ve opened a feature request for my specific use-case: