Support for Postgres HSTORE and JSON data types

stanjackson
stanjackson Member
in Archive

Hi,

 

I am new to Domo.

 

My company provides a multi-tenant SAAS service and we support custom fields on a per client basis.

Our implementation uses the Postgres HSTORE data type (similar to JSON, large text field with key-value pairs).

So, data for one client may include data with keys 'fruits' and 'vegetables' where another client may include data with keys 'trains', 'planes', and 'automobiles'.

 

The Postgres connector converts hstore data types to string. For example, the custom_field data will look something like this:

"fruits"=>"applest", "vegetables"=>"peas"

Now, if I want to import data for a specific key, I can include something like

 

select .... custom_field->'fruits' as fruits

Is there any way in Domo to make procesing this data easier?

Ideally, I would love to be able to directly import hstore data and to subsequently build Magic ETL's or beast mode calculations to pull out specific keys.

Failing that, is there a clever beast mode calculation that makes this easier?

 

Or is there a javascript or python plugin that can be installed to handle this?

 

Thanks,

Stan

Comments

  • Unknown
    Unknown

    Hi all,

    Can anybody help out @stanjackson?

    Thanks!

  • user07401
    user07401 Member

    I am also looking for an answer to this question. I need to be able to process hstores either in Magic ETL or BeastMode.

  • Unknown
    Unknown

    I would love a solution to this as well -- a lot of databases are now storing JSON as a data type, it would be great if Domo had a native way to handle that.

  • Unknown
    Unknown

    If you have properly formatted JSON in a text field, use the RedShift ETL on that set and you can access the variables in the JSON directly. Use this command:

    SELECT json_extract_path_text(text_field,'level1 name', 'level2 name', ...);

     

    For instance:

    SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}','f4', 'f6')

    will return: star.

Categories