Nested JSON objects are flattened by default by Jitsu, even if custom JSONB type is set

[saadhypng]

Environment

• Deploy method: Jitsu Cloud
• Jitsu version: for instance 1.37.5
• OS: for instance Mac OS BigSur

Description

I am trying to override a column type in js transform and set the nested json objects type to jsonb.
But Jistu is by default flattening all the properties of the nested object.

Steps to reproduce

Steps to reproduce the behavior:
JS transform code

if($.event_type == "user_identify"){
    return null
}
if($.event_type == "account_identify"){
    return null
}
else{
    if ('userID' in $){
        $.external_id=$.userID
    }
    else{
        $.external_id=$.user.id
    }
    if ($.src!="jitsu"){
        $.src="segment"
    }

    return {...$, JITSU_TABLE_NAME: "custom_events", __sql_type_properties:"jsonb"}
}

Input event JSON

{
  "__HTTP_CONTEXT__": {
    "headers": {
      "accept": [
        "*/*"
      ],
      "accept-encoding": [
        "gzip, deflate, br"
      ],
      "accept-language": [
        "en-GB,en-US;q=0.9,en;q=0.8,he;q=0.7"
      ],
      "cache-control": [
        "no-cache"
      ],
      "connection": [
        "close"
      ],
      "content-length": [
        "793"
      ],
      "content-type": [
        "application/json"
      ],
      "origin": [
        "http://localhost:3000"
      ],
      "pragma": [
        "no-cache"
      ],
      "referer": [
        "http://localhost:3000/"
      ],
      "sec-ch-ua": [
        "\".Not/A)Brand\";v=\"99\", \"Google Chrome\";v=\"103\", \"Chromium\";v=\"103\""
      ],
      "sec-ch-ua-mobile": [
        "?0"
      ],
      "sec-ch-ua-platform": [
        "\"macOS\""
      ],
      "sec-fetch-dest": [
        "empty"
      ],
      "sec-fetch-mode": [
        "cors"
      ],
      "sec-fetch-site": [
        "cross-site"
      ],
      "user-agent": [
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36"
      ],
      "x-forwarded-for": [
        "115.186.141.21, 172.17.0.1"
      ],
      "x-forwarded-host": [
        "t.jitsu.com"
      ],
      "x-forwarded-port": [
        "443"
      ],
      "x-forwarded-proto": [
        "https"
      ],
      "x-real-host": [
        "t.jitsu.com"
      ],
      "x-real-ip": [
        "115.186.141.21"
      ]
    }
  },
  "_timestamp": "2022-07-19T11:52:31.225630Z",
  "api_key": "js.boey90y15xhc5169oqbdd.4niq9objmadungkwgeqdso",
  "click_id": {},
  "doc_encoding": "UTF-8",
  "doc_host": "localhost",
  "doc_path": "/app/dashboard",
  "doc_search": "",
  "event_type": "team_member_added",
  "eventn_ctx_event_id": "d00e787f-47bd-4910-aed3-ec45e31cdff6",
  "ids": {},
  "local_tz_offset": -300,
  "page_title": "Dashboard",
  "project": "Aw1223444",
  "properties": {
    "workspace_id": "A1111"
  },
  "referer": "",
  "screen_resolution": "1440x900",
  "source_ip": "115.186.141.21",
  "src": "jitsu",
  "url": "http://localhost:3000/app/dashboard",
  "user": {
    "anonymous_id": "vbpbfu6k20",
    "email": "a@b.com",
    "hashed_anonymous_id": "df1137ca740005d0ceb5e8ce7dee2fa6",
    "id": "61d46f4943ca3b0230af56a3"
  },
  "userID": "A112DDD",
  "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36",
  "user_language": "en-GB",
  "utc_time": "2022-07-19T11:52:28.341000Z",
  "utm": {},
  "vp_size": "1440x900"
}

Expected behavior

The nested properties object in the event should be stored as it is in a JSONB column

Actual behavior

The nested object has been flattened by JITSU.

[absorbb]

@saadhypng
You may avoid flattening by converting nested json to string:
$.properties = JSON.stringify($.properties)
this way jsonb type will be properly applied

[saadhypng]

@absorbb Works like a charm but the type of the column is showing as JSON, not jsonb, is jsonb supported as a datatype in Jitsu for Postgres destination?

[absorbb]

@saadhypng
it is strange. I've reproduced jsonb in my test. Maybe the tool you use to show table schema doesn't distinguish json types.
Can you please check with following query:

SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
        table_name = 'custom_events' and column_name = 'properties';

[saadhypng]

There was an error in transform I have fixed it now it's working fine the type is showing as jsonb.

@absorbb Thanks for helping out

[vklimontovich]

@absorbb let's keep this open anyway. The best solution would be stop flattening at any node that has an explicit typing (it could be JSON, TEXT or any other type