Setting JSON copy option

[glefloch]

I have a small DBT project that read from a complex json and write some aggregated data in a JSON file.

The goal is to be able to use this in a web app.

The produced JSON contains one object per line but they are not embedded in an array.

In the DuckDB, it's possible to set the array=true flag in the copy command options.
How can this be done in DBT config ?

I'm willing to contribute if needed :)

[glefloch]

I was able to find a way out by using a post-hook that execute a macro like:

{% macro export_to_json(table) %}
COPY (SELECT * FROM {{ table }})
TO 'target/{{table}}.json'
( ARRAY true )
{% endmacro %}

And in my dbt_project.yml: post-hook: "{{ export_to_json(this.name) }}"

[samle1e]

Thank you! This is exactly what I was looking for, and it worked well.

[jwills]

Ack I'm so sorry I missed this @glefloch -- I'm better at keeping track of issues than discussions-- will remedy that.

[glefloch]

No problem ;) Do you think this could be integrated to dbt-duckdb ?

[jwills]

Oh does using config(materialized='external', format='json', options={'array': True}) not do the trick already?

[samle1e]

That works! Thanks!