Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Ability to access fields in JSON Dicts in Postgres driver #708

Closed
agilliland opened this issue Jul 13, 2015 · 47 comments
Closed

Ability to access fields in JSON Dicts in Postgres driver #708

agilliland opened this issue Jul 13, 2015 · 47 comments
Assignees
Labels
Administration/Metadata & Sync .Completeness Database/Postgres Priority:P1 Security holes w/o exploit, crashing, setup/upgrade, login, broken common features, correctness Querying/Processor Type:New Feature
Milestone

Comments

@agilliland
Copy link
Contributor

agilliland commented Jul 13, 2015

⬇️ Please click the 👍 reaction instead of leaving a +1 or 👍 comment

@kfarr
Copy link

kfarr commented Apr 27, 2016

Yes, similar need here. We have user-supplied metadata stored as json blob. The schema / format is consistent across the entire DB but the data is not "important" enough for us to create columns for each of these items. But of course the customers still want to be able to query on this data. We have been able to get raw postgres SQL queries on JSON working fine, the next prize is allowing non-technical end-users to be able to construct these queries through the UI instead.

@camsaul camsaul self-assigned this Oct 18, 2016
@camsaul camsaul added this to the 0.21.0 milestone Oct 18, 2016
@camsaul camsaul removed this from the 0.21.0 milestone Nov 15, 2016
@LukeAbell

This comment has been minimized.

1 similar comment
@antonmedv

This comment has been minimized.

@camsaul
Copy link
Member

camsaul commented Feb 23, 2017

Hey everyone, one way we prioritize issues is by sorting them by the number of 👍 reactions on each issue's description. So instead of leaving +1 or 👍 comments, please just upvote by adding a reaction to the issue description.

@camsaul camsaul removed their assignment Apr 10, 2017
@avkonst
Copy link

avkonst commented Jul 22, 2017

I have got similar use case when SQLite database stores json text in string based column. Would be good to have it supported too.

@lsh-0
Copy link

lsh-0 commented Aug 15, 2017

we convert article xml documents into json for feeding downstream services. one of these downstream services is a reporting system hooked up to metabase and maintains a copy of these article-json files. some of our powerusers would love to just bypass my carefully denormalised tables and query the json directly

@spencermwatts

This comment has been minimized.

@alfonsolzrg
Copy link

+1. Although we'd love to have full JSON structured query support, in most of our use cases, we can work with a simple "contains".

@conanbatt

This comment has been minimized.

@brylie
Copy link

brylie commented Nov 17, 2017

@alfonsolzrg are you able to get a 'contains' query to work with a JSONB field? I tried using 'contains' on a JSONB field and got the following error:

ERROR: operator does not exist: jsonb ~~ character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 983

@lukabirsa
Copy link

Please at least add contains support to jsonb fields. Full jsonb support would be perfect.

Our use case:

We store device telemetry data in jsonb field. These include device id, device parameters (booleans), device sensors (integers, floats). It would be great if we could unpack the values to perform queries such as "what is the average of humidity sensor for devices that have heating enabled".

Right now all we can do is take the data and export it via CSV to analyze it somewhere else.

@brylie
Copy link

brylie commented Nov 29, 2017

Contains support seems like a great start. However, we have queries that also do other comparisons such as greater than.

Since many of our tables contain JSONB colums, due to various pragmatic decisions during development, we are struggling with SQL syntax (e.g. "->" vs. "->>") any time we want to use JSONB data.

@lukabirsa
Copy link

Just a follow-up: we're currently addressing this issue by creating a stored question that contains SQL which unpacks the values in a tabular form (normalizes data).

You can then use the stored question for people to run queries on. Right now there is an issue with metadata re: column types loss for stored queries (so many aggregations do not work), but this should solve the issue short term.

It would be even better if the question engine would be able to handle this.

howonlee added a commit that referenced this issue Mar 23, 2022
This is the first vertical slice of #708. There is a material ways to go, including mysql implementation, plinking away at the data model stuff, and frontend. There are also big putative sync speed gains I think I should chip away at.
howonlee added a commit that referenced this issue Mar 30, 2022
All under the grand path to #708.

Changes field API to be compatible with nfc-paths. Allows joining of nested field columns by shoving the full identifier into the postgres function call. Also fixes arcane macro test bug by not using the macro.
@rlotun rlotun added this to the 0.43 milestone Apr 18, 2022
@luizarakaki luizarakaki linked a pull request Apr 19, 2022 that will close this issue
@luizarakaki luizarakaki removed a link to a pull request Apr 19, 2022
@luizarakaki
Copy link
Contributor

Postgres semi-structured records (as known as maps and dictionaries) are now supported in Metabase version 43.

We are planning to extend support for other databases in 44.
Arrays are not yet supported.

@lourencogui
Copy link

Is there any docs showing how to use this feature?

@lukas-at-harren
Copy link

@lourencogui Write some SQL?
https://www.postgresql.org/docs/9.3/functions-json.html

@luizarakaki
Copy link
Contributor

luizarakaki commented May 18, 2022

If you have json or jsonb fields in your Postgres databases, we will try to parse them and extract keys. There are no additional steps. The parsed keys behave like any other field.

From 43.1 we parse the first 500 rows of each JSON field to extract keys. If a key is not appearing in the data model, make sure that the key is available in the first 500 rows (can be null).
https://www.metabase.com/docs/latest/administration-guide/databases/postgresql.html#note-on-syncing-records-that-include-json

@ivanspog
Copy link

ivanspog commented Jun 6, 2022

@luizarakaki

From 43.1 we parse the first 500 rows of each JSON field to extract keys. If a key is not appearing in the data model, make sure that the key is available in the first 500 rows (can be null).

We run 43.1 with PostgreSQL DB but I don't see Metabase extracting any keys from JSON column. Are there any steps we need to do to make it happen?

@caleb15
Copy link

caleb15 commented Jun 8, 2022

Same issue, I don't see this feature working in any of our tables that have JSON. We have key/value pairs in the first 500 rows but it's still appearing the same way as before. We're on metabase v0.43.2

image

I used the metabase GUI to construct the query, the raw SQL looks something like this:

SELECT "myschema"."my_table"."id" AS "id", "myschema"."my_table"."json_column" AS "json"
FROM "myschema"."my_table"
ORDER BY "myschema"."my_table"."id" DESC
LIMIT 1048575

I'd be happy to hop on a call or give you more details if you're curious.

@flamber
Copy link
Contributor

flamber commented Jun 8, 2022

Make sure that your Postgres column is the type JSON or JSONB. Only objects are unfolded, not Arrays #2974.
Do a manual sync of your Postgres database via Metabase > Admin > Databases > (postgres) > Sync database schema now.
Then check the log if there's errors during sync - if there's errors, then that's likely why it's not showing.

Please use the forum for questions and troubleshooting: https://discourse.metabase.com/

@caleb15
Copy link

caleb15 commented Jun 9, 2022

@flamber This is less "I need help" and more "I want to report a bug". Regardless, I tried creating an discourse account and it didn't work.

Make sure that your Postgres column is the type JSON or JSONB.

We have a jsonb field, but I don't see any JSON parsing.
I tried a manual sync, same issue. I saw an error in the logs, but it was with a different table. Besides that, no issues.

From 43.1 we parse the first 500 rows of each JSON field to extract keys

Is the first 500 rows the first 500 rows sorted by ID asc or simply the first 500 rows that appear in the query?

I checked a different table of ours and the JSON feature works there, so it looks like this feature does have some functionality, albeit buggy and limited. Better than nothing! Note that if you use raw SQL select * FROM ... then the JSON parsed fields do not appear. Also note that there are errors with uppercase tables or schemas with system characters as well.

@howonlee
Copy link
Contributor

howonlee commented Jun 9, 2022

I added the JSON parsing behavior toggle in #22997 but it seems that the default option for existing DB is that the new option that we instantiate isn't defined in DB settings, therefore the parsing doesn't get triggered.

Separately solved on master with #23136, backport incoming. I will file separate bug

@flamber
Copy link
Contributor

flamber commented Jun 9, 2022

@caleb15 Great if you want to report a bug, then please open a new issue with steps-to-reproduce. Otherwise it's going to be impossible for us know what the cause is and how to fix it.

@howonlee #22997 was only added to master, so that is irrelevant and defaults to true anyway.

@nodesocket
Copy link

nodesocket commented Nov 18, 2022

Can I also request SQLite support? Maybe a way to de-construct JSON into fields? My schema is simply:

Screenshot 2022-11-17 at 8 27 47 PM

With an example row being:

23  {"purchase_date": "2021-03-14T17:52:49", "asset": "ETH", "size": 0, "fill_price":0, "exchange": "xyz"}  2021-03-14 17:52:49

UPDATE

Just found the ability to change the data field to JSON. Experimenting more.

Screenshot 2022-11-17 at 8 45 34 PM

@flamber
Copy link
Contributor

flamber commented Nov 18, 2022

@nodesocket No, only Postgres and MySQL are currently supported.

@nodesocket
Copy link

@flamber actually I got this working with SQLite. Essentially, I used a raw SQL query to build a Metabase Model.

The raw SQL query looks like:

SELECT
    id,
    added,
    JSON_EXTRACT(data, '$.purchase_date') AS purchase_date,
    JSON_EXTRACT(data, '$.asset') AS asset,
    JSON_EXTRACT(data, '$.size') AS size,
    ROUND(JSON_EXTRACT(data, '$.fill_price'), 2) AS fill_price,
    JSON_EXTRACT(data, '$.exchange') AS exchange,
    ROUND(JSON_EXTRACT(data, '$.size') * JSON_EXTRACT(data, '$.fill_price'), 2) AS cost
FROM positions
ORDER BY purchase_date ASC

Then, I converted that to a Metabase Model. Still working through all the details but this seems to work so far. I now can filter on asset for example or fill_price. Excited to discover what else I can do with Metabase.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Administration/Metadata & Sync .Completeness Database/Postgres Priority:P1 Security holes w/o exploit, crashing, setup/upgrade, login, broken common features, correctness Querying/Processor Type:New Feature
Projects
None yet
Development

No branches or pull requests