Json format for dataTable using ajax data

ghfarmers

We have a data source which does not provide standard json format that datatable uses. Following is an example:

{
    "@timestamp": "20220722T153302,57Z",
    "@toplevelentries": "6049",
    "viewentry": [{
            "@position": "1",
            "@unid": "F743A6910453353D8625880E005340A1",
            "@noteid": "1AB6",
            "@siblings": "6049",
            "entrydata": [{
                    "@columnnumber": "0",
                    "@name": "MCAN8",
                    "number": {
                        "0": "100"
                    }
                },
                {
                    "@columnnumber": "1",
                    "@name": "MCDL01",
                    "text": {
                        "0": "Gary test 1st Lease Accountant Notification"
                    }
                },
                {
                    "@columnnumber": "2",
                    "@name": "Manager",
                    "text": {
                        "0": "Huang, Gary"
                    }
                },
                {
                    "@columnnumber": "3",
                    "@name": "$13",
                    "text": {
                        "0": "CR"
                    }
                },
                {
                    "@columnnumber": "4",
                    "@name": "MCTOU",
                    "number": {
                        "0": "478.41"
                    }
                },
                {
                    "@columnnumber": "5",
                    "@name": "MCSTYL",
                    "text": {
                        "0": "OPEN"
                    }
                }
            ]
        },
        {
            "@position": "2",
            "@unid": "A606B6BCF46029028625880E005340AD",
            "@noteid": "1AE6",
            "@siblings": "6049",
            "entrydata": [{
                    "@columnnumber": "0",
                    "@name": "MCAN8",
                    "number": {
                        "0": "107"
                    }
                },
                {
                    "@columnnumber": "1",
                    "@name": "MCDL01",
                    "text": {
                        "0": "LUCY CHRISTENSEN TRUST"
                    }
                },
                {
                    "@columnnumber": "2",
                    "@name": "Manager",
                    "text": {
                        "0": "Culver, Brent"
                    }
                },
                {
                    "@columnnumber": "3",
                    "@name": "$13",
                    "text": {
                        "0": "CR"
                    }
                },
                {
                    "@columnnumber": "4",
                    "@name": "MCTOU",
                    "number": {
                        "0": "478.41"
                    }
                },
                {
                    "@columnnumber": "5",
                    "@name": "MCSTYL",
                    "text": {
                        "0": "OPEN"
                    }
                }
            ]
        }
    ]
}

This obvious won't work for following datatable definition:

$(document).ready(function () {
    var table = $('#example').DataTable({
        ajax: 'ajxemp?open',
        columns: [
            {
                className: 'dt-control',
                orderable: false,
                data: null,
                defaultContent: '',
            },
            { data: 'name' },
            { data: 'position' },
            { data: 'office' },
            { data: 'salary' },
        ],
        order: [[1, 'asc']],
    });

I replaced { data: 'name' } ... part with following:
{ viewentry.entrydata[0].number['0'] },
{ viewentry.entrydata[1].text['0'] },
{ viewentry.entrydata[2].text['0'] },
{ viewentry.entrydata[3].text['0']},
{ viewentry.entrydata[4].number['0'] },
{ viewentry.entrydata[5].text['0'] },

but doesn't have any luck. How do you define the column data in order to use our data source? We can't change our format because of the application limitation.

Thanks for your help.

allan

You need to tell DataTables to find your data in the viewentry array using ajax.dataSrc.

So you would use:

ajax: {
  url: ...,
  dataSrc: 'viewentry'
}

Then the columns.data options would be based on that. Eg entrydata.0.text.0.

Allan

ghfarmers

Thanks!. I re-did my datatable definition as follows:

$(document).ready(function () {
    var table = $('#myfarms').DataTable({
       ajax: {
          url: 'myprogramURL',
          dataSrc: 'viewentry'
       }
        columns: [
            {
                className: 'dt-control',
                orderable: false,
                viewentry: null,
                defaultContent: '',
            },
            { viewentry: 'farmnumber' },
            { viewentry: 'farmname' },
            { viewentry: 'manager' },
            { viewentry: 'type' },
            { viewentry: 'acres' },
            { viewentry: 'status' },
        ],
        order: [[1, 'asc']],
    });

I am getting Uncaught SyntaxError: Unexpected identifier at columns: [

My data or viewentry 'farmnumber' should come from '100' in below:

            "entrydata": [{
                    "@columnnumber": "0",
                    "@name": "MCAN8",
                    "number": {
                        "0": "100"
                    }
                },

and 'farmname' should come from 'Gary test 1st Lease Accountant Notification' in below:

                {
                    "@columnnumber": "1",
                    "@name": "MCDL01",
                    "text": {
                        "0": "Gary test 1st Lease Accountant Notification"
                    }
                },

etc.,, etc.

I just don't know how to reference viewentry column data.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

I am getting Uncaught SyntaxError: Unexpected identifier at columns: [

Missing a comma after the ajax object closing bracket - line 7 in the above

Also change:

{ viewentry: 'farmnumber' },

To be:

{ data: 'farmnumber' },

And the same for the other columns.

Allan