skip to Main Content

I have incoming data like the below mentioned:

Input :

{
  "results": {
    "columns": [
      [
        "id",
        "name",
        "type"
      ],
      [
        "id",
        "name",
        "type"
      ],
      [
        "id",
        "name",
        "type"
      ]
    ],
    "rows": [
      [
        "D01",
        "vm01",
        "vms"
      ],
      [
        "D02",
        "vm02",
        "vms"
      ],
      [
        "D03",
        "vm03",
        "vms"
      ]
    ]
  }
}

The columns are the keys and rows are the values and are expected as key-value pairs

Expected output :

[
  {
    "id": "D01",
    "name": "vm01",
    "type": "vms"
  },
  {
    "id": "D02",
    "name": "vm02",
    "type": "vms"
  },
  {
    "id": "D03",
    "name": "vm03",
    "type": "vms"
  }
]

3

Answers


  1. You can use the following shift transformation spec

    [
      {
        "operation": "shift",
        "spec": {
          "*": {
            "columns": {
              "*": {
                "@2,rows": { //go two levels up the tree to get the values of the rows array
                  "*": {
                    "@(0,[&1])": "[#2].@(3,[&2])" //match arrays from their original positions
                  }
                }
              }
            }
          }
        }
      }
    ]
    
    Login or Signup to reply.
  2. Answer with previews jolt tag.

    You can use the following JOLT spec:

    [
      {
        "operation": "shift",
        "spec": {
          "*": {
            "rows": {
              "*": {
                "*": "[&1].@(3,columns[&1][&])"
              }
            }
          }
        }
      }
    ]
    

    How does it work?

    Just go to the rows items level and get all values with * use the following code as the place of the keys

    [&1].@(3,columns[&1][&])
    

    [&1]: Index of the rows items (array of rows). 0, 1, 2

    [&]: Index of the rows items values (arrays of rows array). 0, 1, 2

    @(3,columns): Value of columns. [[], [], []]

    @(3,columns[&1]): Values of columns items. [], [], []

    @(3,columns[&1][&]): Values of columns items field. id, name, type

    Login or Signup to reply.
  3. (Answering the question using its original python tag…)

    If you can get a sequence of pairs like [("id", "D01"), ("name", "vm01"), ("type", "vms")], you can create the desired dict value:

    >>> dict([("id", "D01"), ("name", "vm01"), ("type", "vms")])
    { "id": "D01", "name": "vm01", "type": "vms" }
    

    You can get such lists by mapping zip over each corresponding pair of columns and rows.

    >>> [dict(t) for t in map(zip, x['data']['rows'], x['data']['columns'])]
    [{'D01': 'id', 'vm01': 'name', 'vms': 'type'}, {'D02': 'id', 'vm02': 'name', 'vms': 'type'}, {'D03': 'id', 'vm03': 'name', 'vms': 'type'}]
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search