How to database insert JSON?

[Apple]

{
    "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?

Inserting Data in Main and Sub Table using App Connect Form Repeat

Intro App Connect Form repeat makes it easy to insert or update nested data in your database tables using a single insert or update action. This way you can easily insert data in your main table as well as in its sub tables. In this tutorial we will show you how to store a user and its contacts. The user will be stored in the main table, while its contacts will be stored in a sub table. Database Structure Our database structure is quite simple. We have a main table called users, having an un…

[mebeingken]

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.

[Apple]

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

[mebeingken]

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.

[scalaris]

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.

[Apple]

@scalaris

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.

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:

Database Insert/Update/Delete from JSON

Please consider a database query “users”: [ { "id": 1, "name": "George", "country": "NL" }, { "id": 2, "name": "Teodor", "country": "BG" } ] That’s a JSON representation of our rows. Through a series of steps, I modify the names: [ { "id": 1, "name": "George P.", "country": "NL" }, { "id": 2, "name": "Teodor T.", "country": "BG" } ] Now, I want to UPDATE the databas…