Storing Objects in Tables and Extracting from JSON Strings

[danielpomeranz]

This topic has come up in the past and I think I just came across a pretty good solution!

When working in Tulip apps, you may generate variables of an object list which allows you to have an undefined number of records of a defined structure. Something like this would store a list of material traceability records which could be captured on a step where you might need more than one.

This specific example could be resolved by writing multiple table records instead of generating the list variable, but there are times where this isn’t possible (for example, when working with connector outputs). If necessary, one can then store this entire data chunk in a text field in Tulip like so:

The text will be stored as a JSON representation of the object:

[
   {
      "id":"p6tAQmeTGXvPnLipi",
      "work order":"WO1234",
      "component lot":"L740201",
      "quantity":1,
      "operator id":"0000",
      "timestamp":1701963638
   },
   {
      "id":"dBWJFtkaSBKdPuZhP",
      "work order":"WO1234",
      "component lot":"L734520",
      "quantity":1,
      "operator id":"0000",
      "timestamp":1701963638
   },
   {
      "id":"ZejucDtYZdvvEc7qC",
      "work order":"WO1234",
      "component lot":"L723111",
      "quantity":1,
      "operator id":"0000",
      "timestamp":1701963638
   },
   {
      "id":"zNyrwetPQNWk2CMT6",
      "work order":"WO1234",
      "component lot":"L723111",
      "quantity":1,
      "operator id":"0000",
      "timestamp":1701963638
   }
]

But the problem is that Tulip had no good way to unpack this JSON. Until now! I was messing around and realized that you can utilize a dummy postgresql connector (which most instances have by default) to create a function for extracting this data. The connector takes in a string of text as an input, and utilizes native postgres functionality to transform it into a Tulip object format. I am sure other SQL dialects can do this too. With a connector function like below we can now work with JSON objects in Tulip. This also opens up a ton of possibilities for parsing JSON from external applications. Hopefully someone finds this useful!

-- SQL Snippet
-- For more info go here: postgresql.org/docs/9.3/functions-json.html
select
json_list->'id' as id,
json_list->'work order' as work_order,
json_list->'component lot' as component_lot,
json_list->'quantity' as quantity,
json_list->'operator id' as operator_id,
to_timestamp((json_list->>'timestamp')::int) as "timestamp"
from json_array_elements($json_text$::json) as json_list

[jjj]

I was able to do the same in T-SQL but have not figured out how to replicate in a Tulip SQL connector

[mellerbeck]

Postgres as the universal fix my data is pretty powerful

[thorsten.langner]

Did you also find a practical way to handle one object (not an array) in the expression editor?

I wanted to convert an Object to text, but the object is not selectable in the expression editor.
I only can find the individual variables inside of it.

My Workaround was, to first copy the object variable, make it an object list (array) and then:
--> clear the array --> push object to the array --> make the array a text --> replace '[' and ']' --> done)
This feels a bit to bulky and “workaroundy” to me… is there a better option?

[danielpomeranz]

Ya, this just looks like a weird oversight by Tulip with their object handling.

You are not able to access Objects in expressions, only their fields. But you are able to access Object Lists.