Support for Postgres HSTORE and JSON data types
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
-
Hi all,
Can anybody help out @stanjackson?
Thanks!0 -
I am also looking for an answer to this question. I need to be able to process hstores either in Magic ETL or BeastMode.
0 -
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.
0 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive