# Macro to load json from file

**URL:** <https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632>\
**Category:** Help\
**Created:** [December 14, 2022, 11:04pm UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632 "2022-12-14T23:04:02Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![venkatm](https://avatars.discourse-cdn.com/v4/letter/v/85f322/32.png) [@venkatm](https://discourse.getdbt.com/u/venkatm)\
**Post date:** [December 14, 2022, 11:04pm UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632/1 "2022-12-14T23:04:02Z")

</div>

The fromjson macro expects the input to be a json string.

Are there any macros to load json from a file?  
If there are none, can you help me with pointers on how to write custom macros that do file io?

Thanks.

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [December 15, 2022, 2:09am UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632/2 "2022-12-15T02:09:09Z")

</div>

@venkatm dbt is designed to work with data that’s already in your database, and doesn’t have the ability to do file IO outside of what your warehouse exposes. What sort of JSON files are you trying to work with?

---

<div class="post-metadata">

**Author:** ![venkatm](https://avatars.discourse-cdn.com/v4/letter/v/85f322/32.png) [@venkatm](https://discourse.getdbt.com/u/venkatm)\
**Post date:** [December 22, 2022, 3:08am UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632/3 "2022-12-22T03:08:42Z")

</div>

Thanks for getting back to me. I was trying to see if I could dynamically construct the SQL query using attributes read from a json file at runtime, without having to modify the dbt model file.

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [January 9, 2023, 9:14pm UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632/4 "2023-01-09T21:14:23Z")

</div>

Depending on the number of attributes you need to manage, you might be able to use vars and the fact that you can pass those into a dbt invocation: [Project variables | dbt Developer Hub](https://docs.getdbt.com/docs/build/project-variables#defining-variables-on-the-command-line)

Can you post an example of the sort of query you want to generate, and which parts of it would vary based on the attributes? It’s hard to be much help in a hypothetical scenario

---

<div class="post-metadata">

**Author:** ![mroy](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/mroy/32/3094_2.png) [@mroy](https://discourse.getdbt.com/u/mroy)\
**Post date:** [September 7, 2024, 7:08am UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632/5 "2024-09-07T07:08:44Z")

</div>

@joellabes: In our case, we want to store large configurations _ **as code** _ within our dbt project. The configs contain metadata about our project, our database, and much more, which come in very handy to dynamically generate stuff in models, tests, macros, etc.

We currently store those configs in a macro ([see related thread here](https://discourse.getdbt.com/t/how-can-dbt-read-files-and-use-it-when-refresh-model/8908)), but it would be very nice to be able to store them in a YAML file instead (it would at least provide better IDE support to edit the file).

What would be even better is if dbt provided a way to store custom configs directly within the project itself, perhaps as a `custom_conf` key in `dbt_project.yml`. But currently that returns an error:

```auto
Runtime Error
  at path []: Additional properties are not allowed ('custom_conf' was unexpected)

```

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [September 25, 2024, 3:55am UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632/6 "2024-09-25T03:55:30Z")

</div>

Would [Defining vars, folder-level configs outside dbt\_project.yml · Issue #2955 · dbt-labs/dbt-core · GitHub](https://github.com/dbt-labs/dbt-core/issues/2955) help?

---

<div class="post-metadata">

**Author:** ![mroy](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/mroy/32/3094_2.png) [@mroy](https://discourse.getdbt.com/u/mroy)\
**Post date:** [September 25, 2024, 5:30am UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632/7 "2024-09-25T05:30:56Z")

</div>

Actually, we have now moved to storing these configs in a project variable inside of `dbt_project.yml`! I think that’s the way to go! 👍

Not sure why we didn’t do that in the first place. 😅 🤷‍♂️ I guess it felt weird since we usually thought of vars as command line key:value things, not large key:dictionary things.

Like this:

```yaml
vars:
  configs:
    key: value
    key2: value2
    key3:
      a: 1
      b: 2
      ...

```

It does make the file much larger, but that’s just a matter of using an IDE which supports code folding. 🙂

And we can use YAML anchors, which is awesome! 👌

---

<div class="post-metadata">

**Author:** ![mroy](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/mroy/32/3094_2.png) [@mroy](https://discourse.getdbt.com/u/mroy)\
**Post date:** [September 25, 2024, 5:39am UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632/8 "2024-09-25T05:39:44Z")

</div>

@joellabes: That being said, do you know if there’s some kind of maximum size for dbt variables? I couldn’t find anything in the documentation.

I tried creating a really large dummy var (a dict with over 10K keys), and it worked. 🤷‍♂️

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [September 25, 2024, 5:43am UTC](https://discourse.getdbt.com/t/macro-to-load-json-from-file/5632/9 "2024-09-25T05:43:09Z")

</div>

I expect you’ll hit comprehensibility limits before you hit any size limit!