Hello! I have a data source that exports data in the form of an object that contains a list of objects. Is it possible to store this data using a rational database like Postgres or SQLite? I noticed it works just fine for jsonl or yaml. For reference the tap I'm using is tap-shipstaion.

Hi @alex_vining!

Is it possible to store this data using a rational database like Postgres or SQLite?

Yes, there’s target-postgres and target-sqlite. Can you say more about your use case?

Yes, my apologies! Here is the schema declared in the tap-shipstation package

json 
orders.json

{
  "type": ["null", "object"],
  "additionalProperties": false,
  "definitions": {
	  ...
	  "OrderItem": {
	    "type": ["null", "object"],
	    "properties": {
		    "orderItemId": { "type": ["null", "integer"] },
			"lineItemKey": { "type": ["null", "string"] },
			"sku": { "type": ["null", "string"] },
			"name": { "type": ["null", "string"] },
			"imageUrl": { "type": ["null", "string"] },
			"weight": { "$ref": "#/definitions/Weight" },
			"quantity": { "type": ["null", "integer"] },
			"unitPrice": { "type": ["null", "number"] },
			"taxAmount": { "type": ["null", "number"] },
			"shippingAmount": { "type": ["null", "number"] },
			"warehouseLocation": { "type": ["null", "string"] },
			"options": {
				"type": ["null", "array"],
				"items": { "$ref": "#/definitions/ItemOption" }
			},
			"productId": { "type": ["null", "integer"] },
			"fulfillmentSku": { "type": ["null", "string"] },
			"adjustment": { "type": ["null", "boolean"] },
			"upc": { "type": ["null", "string"] },
			"createDate": { "type": ["null", "string"] },
			"modifyDate": { "type": ["null", "string"] }
		}
    },
    ...
  },
  "properties": {
    "orderId": { "type": ["null", "string"] },
    "orderNumber": { "type": ["null", "string"] },
    "orderKey": { "type": ["null", "string"] },
    "orderDate": { "type": ["null", "string"] },
    "createDate": { "type": ["null", "string"] },
    "modifyDate": { "type": ["null", "string"] },
    "paymentDate": { "type": ["null", "string"] },
    "shipByDate": { "type": ["null", "string"] },
    "orderStatus": { "type": ["null", "string"] },
    "customerId": { "type": ["null", "string"] },
    "customerUsername": { "type": ["null", "string"] },
    "customerEmail": { "type": ["null", "string"] },
    "billTo": { "$ref": "#/definitions/Address" },
    "shipTo": { "$ref": "#/definitions/Address" },
    "items": {
      "type": ["null", "array"],
      "items": { "$ref": "#/definitions/OrderItem" }
    },
    "orderTotal": { "type": ["null", "number"] },
    "amountPaid": { "type": ["null", "number"] },
    "taxAmount": { "type": ["null", "number"] },
    "shippingAmount": { "type": ["null", "number"] },
    "customerNotes": { "type": ["null", "string"] },
    "internalNotes": { "type": ["null", "string"] },
    "gift": { "type": ["null", "boolean"] },
    "giftMessage": { "type": ["null", "string"] },
    "paymentMethod": { "type": ["null", "string"] },
    "requestedShippingService": { "type": ["null", "string"] },
    "carrierCode": { "type": ["null", "string"] },
    "serviceCode": { "type": ["null", "string"] },
    "packageCode": { "type": ["null", "string"] },
    "confirmation": { "type": ["null", "string"] },
    "shipDate": { "type": ["null", "string"] },
    "holdUntilDate": { "type": ["null", "string"] },
    "weight": { "$ref": "#/definitions/Weight" },
    "dimensions": { "$ref": "#/definitions/Dimensions" },
    "insuranceOptions": { "$ref": "#/definitions/InsuranceOptions" },
    "internationalOptions": { "$ref": "#/definitions/InternationalOptions" },
    "advancedOptions": { "$ref": "#/definitions/AdvancedOptions" },
    "tagIds": {
      "type": ["null", "string"],
      "items": { "type": ["null", "integer"] }
    },
    "userId": { "type": ["null", "string"] },
    "externallyFulfilled": { "type": ["null", "boolean"] },
    "externallyFulfilledBy": { "type": ["null", "string"] }
  }
}

Here is the sqlite schema created from target-sqlite: ```sql sqlite> .schema CREATE TABLE shipments ( ... ); CREATE TABLE orders ( advanced_options__bill_to_account VARCHAR, advanced_options__bill_to_country_code VARCHAR, advanced_options__bill_to_my_other_account VARCHAR, advanced_options__bill_to_party VARCHAR, advanced_options__bill_to_postal_code VARCHAR, advanced_options__contains_alcohol BOOLEAN, advanced_options__custom_field1 VARCHAR, advanced_options__custom_field2 VARCHAR, a…

Yeah, not all targets implement record flattening. Anyway, the most common practice is to flatten or normalize the data downstream in the transformation stage.

Ill look into this further, thank you!