Homepage

[DEPRECATED] Data Import using JSON

Last edit: Dec 12, 2023

This topic describes data import using JSON format

Warning

Import using JSON format is deprecated. We recommend importing using CSV format

Data to be imported needs to be in JSON format, it is an array of user data and array of records data:

{
  "users": [...],
  "records": [...]
}

You can generate data from an existing Instance with the pos-cli data export command (read more about this here). It will generate the data.json file. You can also import data from other sources (e.g. old stack, other systems) once you generate the proper JSON file. It is fairly easy to convert, for example, a CSV file into a JSON file.

General information

  • Before import, your Tables need to be deployed.

  • When importing data, the id fields will be changed. Relations will be kept. If you have your own relations defined in properties, you need to first describe them in Table files - read more about this here.

  • If you test your imports and need to clean Instance data, use the pos-cli data clean command. Note, that this command will work only on staging Instances.

  • When you run import twice, it will try to match previously imported data without removing existing changes. Example:

    1. You have a record shirt with properties: color, size
    2. First import adds new shirt with JSON: { "id": 1, "properties": { "color": "red" }}
    3. User sets attributes of shirt, now shirt has the following properties: { "color": "green", "size": "large" }
    4. Second import with the same file changes shirt object to: { "color": "red", "size": "large" }
  • Import will only do basic validations, such as the existence of properties and type validation. It doesn't check any of the validations defined on forms.

Data Import with the CLI

With pos-cli data, you can export/import Instance data (information entered by the users).

platformOS CLI provides the pos-cli data import command for uploading and importing data from a JSON file or ZIP archive with CSV files. It accepts one option:

  • --path (short: -p): A path to a JSON or ZIP files to be imported

Under the hood, the CLI uses the GraphQL import API that's described in the next section. However, using the CLI provides some major benefits:

  • Uploading file from disk
  • Leveraging the CLI's authentication mechanism (i.e. you don't need to manually send your authentication token)

Input format

The required data format is a JSON file with users and records keys that have an array of maps. Each of those collections needs to be in the same format as a corresponding GraphQL mutation. Under the hood import is just a set of mutations: import_users(), import_records(). When importing data using the CLI, the file should contain data in a format that matches the following structure:

{
  "users": [<UserImport],
  "records": [<CustomizationImport>],
}

Example

Example data import file data.json

{
  "users": [
    {
      "id": 1000,
      "email": "tom@example.com",
      "first_name": "Tom",
      "slug": "tom"
    },
    {
      "id": 1001,
      "email": "mike@example.com",
      "first_name": "mike",
      "slug": "mike",
      "created_at": "2010-10-10 10:00:00",
      "profiles": [
        {
          "type_name": "student",
          "id": 2001,
          "properties": {
            "age": 21
          },
          "attachments": [
            {
              "name": "document",
              "url": "files/document.pdf"
            }
          ],
          "images": [
            {
              "name": "avatar",
              "url": "files/avatar.png"
            }
          ]
        }
      ]
    }
  ],
  "records": [
    {
      "id": 1,
      "user_id": 1000,
      "type_name": "house",
      "customizable_id": 1000,
      "customizable_type": "User",
      "properties": {
        "color": "red"
      }
    },
    {
      "id": 2,
      "user_id": 1001,
      "type_name": "house",
      "customizable_id": 1001,
      "customizable_type": "User",
      "properties": {
        "color": "green",
        "buyer_id": 1000,
        "beach_house_id": 1
      },
      "addresses": [
        {
          "name": "foo_address",
          "address": "Warsaw",
          "postcode": "01-001"
        }
      ]
    },
    {
      "id": 3,
      "user_id": 1001,
      "type_name": "house",
      "properties": {
        "buyer_id": 1000,
        "beach_house_id": 2
      },
      "attachments": [
        {
          "name": "driver_licence",
          "url": "files/licence.pdf"
        }
      ],
      "images": [
        {
          "name": "headshot",
          "url": "files/headshot.png"
        }
      ]
    }
  ]
}

To import the data from a file, run the following command in the terminal:


pos-cli data import --path=data.json staging-instance

Data Import with GraphQL mutations

It is possible to import data using GraphQL mutations. This gives you more flexibility, and even allows you to create your own import tool as a website feature. Please remember to handle authorization properly.

There are two main mutations for importing import_users() and import_records(). You can create a page that will import sent data.

app/graphql/import_data.graphql
mutation import_data(
    $users: [UserImport!]!,
    $records: [CustomizationImport!]!
  ) {
  import_users(users: $users) { ids }
  import_records(records: $records) { ids }
}
app/views/pages/import_data.json.liquid

---
method: post
---
{% graphql res = "import_data",
  users: context.params.users,
  records: context.params.records
%}

{{ g }}


curl -d @transformed.json \
  -H "Content-Type: application/json" \
  -X POST https://example.com/import_data.json

Data Import with raw import API

Our CLI uses the HTTP API to import a data file. You can use it in your own tool.
Please note, that you need to provide your authentication token in the HTTP Authorization header of the request.


curl -F "import[data]=@data.json" \
  -H "Authorization: Token token=[YOUR API TOKEN]" \
  -X POST https://example.com/api/app/imports

Note

You can find your API Token in Partner Portal under "Access Key".

Importing Images and Files

Records and Users can store images and attachments in properties (Visit User Uploads). To import them you need to have the proper data record in *.yml files. Here is an example record with images and attachments:

name: car
properties:
- name: color
  type: string
- name: car_document
  type: file
- name: car_photo
  type: image

Uploading files with CLI

To import files with pos-cli, prepare a JSON file with paths to existing files. You need to have those files on your disk. Read more about the required structure in our reference docs. Here is an example JSON file that assumes you have files in the import_files/ directory:

{
  "records": [
    {
      "id": "id",
      "type_name": "car"
      "user_id": null,
      "properties": {
        "color": "red"
      },
      "attachments": [
        {
          "url": "import_files/offer.pdf",
          "name": "car_document"
        }
      ],
      "images": [
        {
        "url": "import_files/car.png",
        "name": "car_photo"
        }
      ]
    }
  ]
}

During the import, pos-cli will upload the files to our storage and match them with corresponding records.

Record relations

With properties, you can create your columns for storing the data. In the properties, you can also store the id of an existing object. Then you can fetch the data with related_record(join_on_property: String!) or the related_user(join_on_property: String!) field in GraphQL.

In order to export/import data with relations preserved, you need to define them in Table files using the belongs_to attribute.

app/schema/house.yml
name: house
properties:
- name: address
  type: string
app/schema/cars.yml
name: cars
properties:
- name: color
  type: string
- name: house_id
  type: integer
  belongs_to: house

This works only with JSON import/export and hence is deprecated.

Questions?

We are always happy to help with any questions you may have.

contact us