Agent: json

Reads JSON data from a file and inserts rows into the target database table.

Warning

This agent does not create database tables. Target table must already exist.

The target table is determined by task_id.

Typical flow with REST API repeat:

restapi -> creates JSON response files + index file
json    -> reads index file, loads JSON files into target table, deletes processed files

Using the same origin file in several JSON actions

json agent consumes files after successful import. If the same origin file must be processed by several json actions, each action should get its own copy.

Copying repeated API result sets with index files is not supported yet.

Dependency

This agent uses JMESPath for reading JSON values.

Install if needed:

pip install jmespath

Syntax and samples

Basic example:

- do: json
  origin:
    type: task
    file: response.json # normal JSON file

  cols:
  - name: id
    from: data # data, params, extract, const
    data: arno_id # JMESPath for params/data/extract, literal value for const
    type: int

  - name: email
    from: data
    data: contact.email # nested values use JMESPath
    type: text

  - name: import_source
    from: const
    data: manual_file
    type: text

Index file example:

- do: json
  origin:
    type: task
    file: unprocessed_files.json # index file from restapi repeat
    file_is_index: true # optional, true if origin.file is index file

  cols:
  - name: jrk
    from: params # values from index entry params
    data: jrk
    type: int

  - name: id
    from: data # current JSON row
    data: arno_id
    type: int

  - name: email
    from: data
    data: contact.email
    type: text

Child row example:

- do: json
  origin:
    type: task
    file: unprocessed_files.json
    file_is_index: true

  extract: programs[] # optional, JMESPath for child rows from each data row

  cols:
  - name: school_id
    from: data # parent/root row
    data: arno_id
    type: int

  - name: program_name
    from: extract # current child row from extract
    data: name
    type: text

  - name: grades
    from: extract
    data: grades
    type: integer[]

File origin

The JSON file is defined under origin.

origin uses the common file pointer structure. See File pointers.

Required:

origin
origin.type
origin.file

For type: fs, origin.dir is also required.

For type: task, origin.dir is optional. If missing, current task directory is used.

Normal JSON file:

origin:
  type: task
  file: response.json

Index file:

origin:
  type: task
  file: unprocessed_files.json
  file_is_index: true

Normal JSON file

If file_is_index is missing or false, origin.file is treated as a normal JSON file.

Example JSON:

[
  {
    "arno_id": 12,
    "name": "Tallinna Katse Kool",
    "contact": {
      "email": "info@katse.ee"
    },
    "active": true
  },
  {
    "arno_id": 13,
    "name": "O'Brien Testkool",
    "contact": {},
    "active": false
  }
]

Example action:

- do: json
  origin:
    type: task
    file: schools.json

  cols:
  - name: arno_id
    from: data
    data: arno_id
    type: int

  - name: nimi
    from: data
    data: name
    type: text

  - name: email
    from: data
    data: contact.email
    type: text

  - name: aktiivne
    from: data
    data: active
    type: bool

If JSON root is a list of objects, every object becomes one database row.

If JSON root is one object, it becomes one database row.

After successful import, the origin JSON file is deleted.

Index file

If file_is_index: true, origin.file is treated as an index file.

Index file is usually created by restapi repeat logic.

Example unprocessed_files.json:

[
  {
    "file": "work/routes/test_route/public/schools/restapi_20260721_134739_579796.json",
    "params": {
      "jrk": 17
    }
  }
]

Each index entry must have:

file

Optional:

params

params can be used in column mappings with from: params.

Example:

- name: jrk
  from: params
  data: jrk
  type: int

After a response file is successfully imported:

processed JSON file is deleted;
entry is removed from index file;
if index file becomes empty, index file is deleted.

If index file is missing, it is treated as already consumed.

JMESPath

JSON values are read with JMESPath.

Nested object value:

- name: email
  from: data
  data: contact.email
  type: text

Array value:

- name: tags
  from: data
  data: tags
  type: text[]

Full object or list as JSONB:

- name: raw_programs
  from: data
  data: programs
  type: jsonb

Extract

extract is optional.

It is used when target table should be filled from a child structure inside every data row.

Example JSON:

[
  {
    "arno_id": 12,
    "name": "Tallinna Katse Kool",
    "programs": [
      {
        "program_id": 101,
        "name": "Põhiõpe",
        "grades": [1, 2, 3, 4]
      },
      {
        "program_id": 102,
        "name": "Keelekümblus",
        "grades": [5, 6, 7]
      }
    ]
  },
  {
    "arno_id": 13,
    "name": "Programmita Kool",
    "programs": []
  }
]

Example action:

- do: json
  origin:
    type: task
    file: schools.json

  extract: programs[]

  cols:
  - name: school_arno_id
    from: data
    data: arno_id
    type: int

  - name: school_name
    from: data
    data: name
    type: text

  - name: program_id
    from: extract
    data: program_id
    type: int

  - name: program_name
    from: extract
    data: name
    type: text

  - name: grades
    from: extract
    data: grades
    type: integer[]

This creates one database row for each item in programs.

Values from from: data come from the parent/root row.

Values from from: extract come from the current extracted child row.

If child structure is missing or empty, no rows are inserted for that parent row.

Column definition

Every item in cols describes one target table column.

Required:

name
from
data

Optional:

type

If type is missing, text handling is used.

Column sources

Supported from values:

data
params
extract
const

Value from current JSON row

- name: nimi
  from: data
  data: name
  type: text

Value from nested object

- name: email
  from: data
  data: contact.email
  type: text

Value from index params

Only useful when origin.file_is_index: true.

- name: jrk
  from: params
  data: jrk
  type: int

Value from extracted child row

Only useful when extract is used.

- name: program_name
  from: extract
  data: name
  type: text

Constant value

- name: import_source
  from: const
  data: restapi_repeat
  type: text

SQL expression:

- name: dwh_imported_ts
  from: const
  data: current_timestamp
  type: sql

Supported value types

The type setting controls how JSON/Python values are written into SQL.

Supported values:

text
int, integer
dec, decimal, numeric, float
bool, boolean
json, jsonb
raw, sql
text[], varchar[]
int[], integer[]
dec[], decimal[], numeric[], float[]
bool[], boolean[]

For list or dict values, use json or jsonb when the whole nested structure should be stored:

- name: metadata
  from: data
  data: metadata
  type: jsonb

For simple lists, use PostgreSQL array types:

- name: grades
  from: extract
  data: grades
  type: integer[]

Remarks

One json action loads one target table.
The target table name comes from task_id.
The agent inserts data into existing tables. It does not create or alter table structure.
Origin JSON root must be one object or a list of objects.
Scalar root rows are not supported.
Missing JMESPath value becomes NULL.
extract must return an object or list.
Missing or empty child list is not an error.
Normal origin JSON file is deleted after successful import.
Index file is updated after every successfully processed file.
Empty index file is deleted.
Duplicate handling, upsert logic and database constraints are handled outside this agent.
post_action is not implemented yet.