How would the schema be defined for something like ``` id 7e Meltano #getting-started

How would the schema be defined for something like...

abhishek_ajmera

12/06/2022, 1:22 PM

How would the schema be defined for something like:

Copy code

{
  "id": "7e4967a3-6cde-409d-af14-ffc480f738c7",
  "orderHoldAmount": {
    "0f72cdb7-0ba0-4968-9424-d7abf4415ca0": 1251.25,
    "2bfce02a-9fae-4ebe-8f72-2cb9f05a2a65": 98633.86,
    "2c93f31f-86f3-4e8d-b43b-d34ab38a4d57": 98,
    "9fb720e2-a1cd-4d0e-8ec1-796e43cb32f8": 167077.54,
    "d14ce0cc-67de-4cc3-8bea-8b58cc761367": 1220,
    "e3597c70-2a3a-42f5-8725-994c62113ecb": 200983.2
  },
  "balance": 413813.09
}

where all the keys inside orderHoldAmount are UUIDs that keep getting updated?

abhishek_ajmera

12/06/2022, 1:50 PM

I’m having trouble defining schema for a

record of records

map of maps

in general.

Array of maps

worked though.

edgar_ramirez_mondragon

12/06/2022, 4:32 PM

that’s what additionalProperties
and pattern_properties
are for 🙂. With the SDK:

Copy code

th.PropertiesList(
  th.Property("id", th.StringType),
  th.Property("orderHoldAmount", th.ObjectType(
    additional_properties=th.NumberType,
  )),
  th.Property("balance", th.NumberType),
)

https://sdk.meltano.com/en/latest/typing.html#singer_sdk.typing.ObjectType.__init__

abhishek_ajmera

12/07/2022, 4:20 AM

These are new properties for me, as well as the SDK syntax. Will try to replicate this into JSON format today, thanks!

abhishek_ajmera

12/07/2022, 5:44 AM

Since I don’t have any regular properties and only additionalProperties, I thought the following should work but it doesn’t. 😕

Copy code

"orderHoldAmount": {
            "type": "object",
            "additionalProperties": {
              "type": "number"
            }
          },

Saw some posts saying we need set additionalProperties to true as well, but not sure where that would go. Tried a few ways but didn’t work either.

edgar_ramirez_mondragon

12/07/2022, 4:01 PM

I thought the following should work but it doesn’t

Can you say more about how and in what context it failed? The following schema validates the JSON in your first comment:

Copy code

{
  "type": "object",
  "additionalProperties": false,
  "properties": {
    "id": {
      "type": "string"
    },
    "orderHoldAmount": {
      "type": "object",
      "additionalProperties": {
        "type": "number"
      }
    },
    "balance": {
      "type": "number"
    }
  },
  "required": [
    "id"
  ]
}

validated with: https://www.jsonschemavalidator.net/

abhishek_ajmera

12/08/2022, 2:55 PM

Can you say more about how and in what context it failed?

I’m not really sure what the issue is since the EL doesn’t throw any error, just that the column on BigQuery is full of null values only. And tbh I’m fine with even having the map as a JSON formatted string but unable to get even that as well.

Open in Slack

Previous Next