JSON/JSONB type support (PostgreSQL)

[dnakov]

Amazing tool.
Would be great to support jsonb columns

[eirikbakke]

Thank you! Do you get any errors when trying to view JSONB in a PostgreSQL table? At a bare minimum I should make sure the JSON shows up as text. But ideally I'd support some of the JSON field extractor functions.

[dnakov]

No errors, it works and shows up as text which is already on par with every SQL tool I've seen.

The issue is that if you wanted to use a specific value from the json column like json_data->'column_name', you can't, as there is no place I can see that will allow me to define a custom column other than functions?

I was even thinking maybe I could just edit the SQL or add it there but it's not editable :)

[eirikbakke]

I think the initial way to support JSON value extract would indeed be based on formula functions. You'd add a formula column and say e.g. int(jsonField([json_data], 'column_name')).

So the design would be something like this:

• Add a proper JSON type in Ultorg's type system. Values of this type can be any JSON "value" per the json.org spec (array, object, null, or primitive value).
• In Ultorg's formula language, add a jsonField(jsonValue, keyString) function to extract a value from a JSON object.
• In Ultorg's formula language, add a jsonElm(jsonValue, index) function to extract a value from a JSON array.
• In Ultorg's formula language, make the int and num functions accept a JSON parameter (the text function already does).
• For booleans, just rely on the text function.
• Find some way to test for a JSON "null" value without assuming a specific data type. (I'm trying to avoid adding a large number of use case specific formula functions.)

The missing thing here is an ability to unnest JSON arrays without stating an explicit index. This would be a different kind of non-scalar formula function that is not yet supported, though.

[mstade]

Came here looking to say the same thing – we make fairly heavy use of JSONB in our Postgres databases and it'd be great if it was possible to pick out the relevant fields using formulas, which currently doesn't seem supported. Not sure how it'd work going the other way around, i.e. not just reading and formatting the data in a more human readable way but also editing the data and have it write JSON back in the relevant columns. But even if it's read only that'd be pretty great.

Amazing app, big kudos for trying new things in this space!

[eirikbakke]

@mstade Editing JSON, initially, would probably just work like editing a string value.

In the long term there are more things that could be done with JSON. The interesting thing with JSON is that it has the same kind of nested data model as Ultorg uses to model its query results. But integrating these two concepts too much could also confuse users. Another way to deal with data editing of JSON fields in the future could be to have a more capable pop-up editor for JSON values, e.g. in a separate dialog box. That way it's still clear that you're editing a single primitive value in a database row/column, even though that value can have nested objects/arrays etc.

[rromanowski-figure]

We are big users of JSONS be as well. In the short term, adding the option for indentation would help parse text at a glance.

Once JSONB is supported later, two features I'd like:

• A calculated field (i.e. a simple extracted attribute) for the views
• The ability to join or filter on a JSONB attribute

[eirikbakke]

@rromanowski-figure When you say indentation, do you mean pretty-printing the JSON that comes from the database?

Once there are formula functions for extracting values from JSON, you'd filter by adding a formula column to extract the value and then opening the filter on that column.

[rromanowski-figure]

Yes, pretty-print. Nothing extreme with colors at first, just some line breaks at minimum

And that sounds awesome!

[rvion]

EDIT: I did a mistake here; screenshot is failing because I took an example with a json field, not jsonb;

indeed json has no equality operator, while jsonb does have an equality operator.

running

ALTER TABLE "tableName"
      ALTER COLUMN "columnName"
      SET DATA TYPE jsonb
      USING "columnName"::jsonb;

and refreshing ultorgs perspective worked for this table.
So this message is pretty much irrelevant, beside just saying we would also love to have better json visualisation.

Leaving this message here in case it helps someone who did the same mistake. Feel free to hide it.

---

previous message:

@eirikbakke idem on our end, we love Ultorg, but we use jsonb most perspectives end-up having yellow(with error) columns due to json not working within joins, or groups:

I guess it's because of distinct + json https://stackoverflow.com/questions/48420438/postgresql-could-not-identify-an-equality-operator-for-type-json ?

Minimal json support would be 80% of what we're missing to switch to ultorg for most data inspections

thanks for this magical tool;

[eirikbakke]

@rvion Thanks for the screenshot! I think the current version of Ultorg is generally confused around JSON columns, since I haven't taught it how to deal with that data type at all yet.

I suspect you can work around some of the warnings by adding a formula column and entering "=TEXT([myJsonColumnName])" to convert the JSON to text. Then you can e.g. filter on the computed value. But I do need to add field extraction functions before this becomes useful in most cases.

[eirikbakke]

JSON-typed table columns are now properly supported in Ultorg 1.2.4! This (1) gets rid of the various errors you saw when using json/jsonb columns on PostgreSQL, (2) adds automatic pretty-printing of JSON data in the table layout (with an option to disable), and (3) adds two new formula functions, jsonParse and jsonValue, to convert text to JSON and to extract individual primitive values from the JSON data, respectively. See https://www.ultorg.com/releasenotes/ for details.

The specific design of the jsonValue formula function was based on what is available in the various SQL dialects, since it needs to work on not only PostgreSQL, but also on MySQL/MariaDB, Microsoft SQL Server, SQLite, and eventually also Oracle.

Existing Ultorg users can find the latest version at the old download link. Thanks for reporting this issue!