topic Re: json to table in Talend Studio

json to table

Jayesh47 — Fri, 02 Feb 2024 13:29:41 GMT

Need help in creating table out of this json response:

Columns would be

id | name | pid | -48 | -33 | -39 | 1695378 | 1695379 | ..... so on will be the headers of the table and record would be

7362576 | Call another | TR-44 | 0 | 0 | 0 | 4

basically i need to understand the properties part not able to pivot

{
    "links": [
        {
            "rel": "test-case",
            "href": "https://linhdang.qTestnet.com/api/v3/projects/32029/test-cases/5574277?versionId=6573928"
        },
        {
            "rel": "self",
            "href": "https://linhdang.qTestnet.com/api/v3/projects/32029/test-runs/7362576"
        }
    ],
    "id": 7362576,
    "name": "Call another",
    "pid": "TR-44",
    "properties": [
        {
            "field_id": -48,
            "field_value": "0"
        },
        {
            "field_id": -33,
            "field_value": "0"
        },
        {
            "field_id": -39,
            "field_value": "0"
        },
        {
            "field_id": -59,
            "field_value": "4"
        },
        {
            "field_id": -36,
            "field_value": "1"
        },
        {
            "field_id": 1695378,
            "field_value": "505"
        },
        {
            "field_id": 1695379,
            "field_value": "2016-11-30T17:00:00+00:00"
        },
        {
            "field_id": 1695380
        },
        {
            "field_id": 1695381,
            "field_value": "2016-11-30T17:00:00+00:00"
        },
        {
            "field_id": 1695374,
            "field_value": "5202"
        },
        {
            "field_id": 1695387,
            "field_value": "601"
        },
        {
            "field_id": 1695385,
            "field_value": "723"
        }
    ],
    "test_case_version_id": 6573928,
    "creator_id": 5202
}

Re: json to table

Shicong_Hong — Sun, 04 Feb 2024 19:00:50 GMT

Hello

Extract the column names from Json response, build the 'Create table statement SQL' string on a tJavaFlex component, store the string to a global variable for used later on tXXXRow.

In next subjob, execute the 'create table statement SQL' on tXXXRow to create the table.

In next subjob, extract the field_value from Json response again, denormalize the rows to be only one row and insert it to the table.

I'm afraid the column name cannot begin with a negative sign, it will be replaced with underline, such as "_48".

Can you try and let me know if you have any issues?

Re: json to table

Jayesh47 — Tue, 13 Feb 2024 09:09:05 GMT

Using restclient extractjson and xmap i am able to extract the below data,

if you can help me the last step of data transformation, screenshot added below

Re: json to table

Shicong_Hong — Mon, 19 Feb 2024 02:30:12 GMT

I think tDenormalize component can fit you need.

https://help.talend.com/r/en-US/8.0/processing/tdenormalize-standard-properties