JSON Schema Form to database involving array

App Building
,
1

Hi there,

I am currently building a custom JSON Schema form for my app to insert data into my database and it involves dynamic field generation. See schema below:

{
  "title": "Release Information",
  "type": "object",
  "properties": {
    "releaseTitle": {
      "type": "string",
      "title": "Release Title"
    },
    "trackList": {
      "type": "array",
      "title": "Track List",
      "items": {
        "type": ["string", "null"],
        "default": "Track Name"
      }
    }
    
  }
}

I want to push to a table in a pgsql db that has the columns release_name and track_name. However, I need to iterate the release_name over the number of track_names. At present I can only push the data in as release_name (as string) and track_name (as array if more than one track). Because the Track Name question in the form is dynamic and could have as many as 10 tracks created and then submitted I need the insert into statement to know to duplicate the same number of times as the number of tracks and have each track name inserted as a unique row.

I have seen this is somewhat possible using JS but I am not that skilled with the language unfortunately. Any help would be greatly appreciated!

Let me know if you need any more information!

2

Hey @jopgood I'm happy to try and help you out here! Could you possibly share some screenshots of what your current PostgreSQL query looks like?

Could you also share a screenshot of what your intended final format for your data should look like?

Thank You!

3

Hi @cperea

Thank you for helping out - the correct PostgreSQL query is mostly the initial struggle here.

Realistically I was looking to do a simple INSERT INTO statement using the values generated from the form. However, I'm not quite sure how to write a SQL statement that can account for the undefinable range of answers that the form could generate.

It could be -

INSERT INTO table (release_title, track_name)
VALUES ({{ releaseJSONForm.data.releaseTitle }}, {{releaseJSONForm.data.trackList}})

But of course this results in the track_name column containing an array of elements. These elements need to be split up into unique rows where release_title is duplicated.

For example:

release_title | track_name
--------------------------
release_a | [track_a, track_b, track_c]

into:

release_title | track_name
--------------------------
release_a | track_a
release_a | track_b
release_a | track_c

If that makes sense? I'm hoping to do it all in the retool/sql query but of course I understand if that's not possible. I know I could probably run a script in Python that does it outside Retool for me but hoping to keep it all in one place.

Many thanks