How do I use template directories to seed an entire row, not just a JSON column?

[leojweda]

The example provided in the docs has this in atlas.hcl:

locals {
  # The path is relative to the `atlas.hcl` file.
  seed_users = split("\n", file("seed_users.json"))
}

and this in the migration:

{{ range $line := .seed_users }}
  INSERT INTO `users` (`role`, `data`) VALUES ('user', '{{ $line }}');
{{ end }}

Which outputs:

  -- migrating version 20230719102332
    -> INSERT INTO `users` (`role`, `data`) VALUES ('user', '{"name": "Ariel"}');
    -> INSERT INTO `users` (`role`, `data`) VALUES ('user', '{"name": "Rotem"}');
  -- ok (13.313962ms)

It puts the entire value of the JSON into a single JSON column.

I want to import the entire row's values from a JSON.

The problem is that, if I put jsondecode in atlas.hcl, I get Error: data.template_dir.migrations: attribute "vars" must be a map of strings, numbers or booleans, got: tuple.

Putting it in the migration isn't an option either because I get function "jsondecode" not defined.

Is there a way to do this or should I just use raw SQL to seed the table?

[giautm]

Hey @leojweda, what's the driver you're using? Either MySQL or PG?

[leojweda]

Yes. Postgres.

[giautm]

You can use the json_array_elements to parse the JSON string from variable, then insert into your table. This is batch insert so it should faster than multiple insert statements.

INSERT INTO employees (name, skills)
SELECT (json_data->>'name'), (json_data->'skills')::jsonb
FROM json_array_elements('[
  {"name": "Alice", "skills": ["Java", "SQL"]},
  {"name": "Bob", "skills": ["Python", "Go"]},
  {"name": "Charlie", "skills": ["C++", "Rust"]}
]') AS json_data;

[giautm]

@leojweda Is that help for your case?

[leojweda]

It worked. Thanks!