DataTables - joining data from two json files based off one field

[Deleted User]

Hi There,

I'd like to display data from 2 json files into one datatable based off a single field in both json's - Acquisition_ID is this possible with DataTables?

E.g. If a user enters an acquisition_id the single datatable should show all the available data for that acquisition in the one table.

Survey Data Table

<table id="example3" class="display" cellspacing="0" width="100%">
<thead>
  <tr>
    <th>Acquisition_ID</th>
    <th>Archive_ID</th>
<th>Data_Type</th>
    <th>Description</th>
    <th>Download</th>
  </tr>
</thead>
<tfoot>
    <tr>
     <th>Acquisition_ID</th>
     <th>Archive_ID</th>
 <th>Data_Type</th>
     <th>Description</th>
     <th>Download</th>
    </tr>
    </tfoot>
</table>

Survey Reports Table

<table id="example4" class="display" cellspacing="0" width="100%">
<thead>
  <tr>
    <th>Acquisition_ID</th>
    <th>Archive_ID</th>
    <th>Description</th>
    <th>Download</th>
  </tr>
</thead>
<tfoot>
    <tr>
     <th>Acquisition_ID</th>
     <th>Archive_ID</th>
     <th>Description</th>
     <th>Download</th>
    </tr>
    </tfoot>
</table>

$(document).ready(function() {
$('#example3').DataTable( {
"deferRender": true,
"ajax":{
"url":"https://opendata.arcgis.com/file1.geojson",
"dataSrc": "features"
},
"columns": [
{ "data": "properties.AcquisitionId"},
{ "data": "properties.ArchiveId" },
{ "data": "properties.SurveyDataTypes" },
{ "data": "properties.Description" },
{ "data": "properties.UNCPath", "render": function ( data, type, row, meta ) {
return '<a href="'+data+'">Download</a>';
} },
],
dom: 'lfrtBip',
buttons: [
'copy', 'excel', 'pdf', 'csv'
]
});
});
</script>

<

script type="text/javascript" class="init">

$(document).ready(function() {
$('#example4').DataTable( {
"paging": true,
"deferRender": true,
"ajax":{
"url":"https://opendata.arcgis.com/file2.geojson",
"dataSrc": "features"
},
"columns": [
{ "data": "properties.AcquisitionId"},
{ "data": "properties.ArchiveId" },
{ "data": "properties.Description" },
{ "data": "properties.UNCPath", "render": function ( data, type, row, meta ) {
return '<a href="'+data+'" target="_blank">Download</a>';
} },
],
dom: 'lfrtBip',
buttons: [
'copy', 'excel', 'pdf', 'csv'
]
});
});

Cheers

RockE

kthorngren

The best place for this is in the server script. Combine the data from the two JSON sources into one response.

If a user enters an acquisition_id the single datatable should show all the available data for that acquisition in the one table.

I'm guessing you want to have the user select the acquisition_id somehow (a drop down list maybe) and fetch the appropriate data. There are lots of ways to do thsii but in general you can pass the ID in the URL or use the data option of a jQuery Ajax request. Some will use the ajax.data option of the Datatables ajax option and use ajax.reload().

The server will take this and compile the appropriate data from the two JSON sources and provide one response with the data.

Does this help?

Kevin

[Deleted User]

Hi Kevin,

Just via the standard DataTables search box ideally. "(a drop down list maybe) "

Unfortunately I can't do anything server side, the developers have produced json files and published those individually. Real world they could probably publish a single json with everything in it, unfortunately they don't have the staff to redevelop it.

I was hoping for some javascript or datatables magic to read the files and combine elements prior to populating the table.

Cheers

RockE

kthorngren

I was hoping for some javascript or datatables magic to read the files and combine elements prior to populating the table.

You can use Javascript to combine into one dataset. Without knowing all your solution details I would look at doing something like this:

1. Fetch the first JSON file via jQuery Ajax
2. In the success function fetch the second file via jQuery Ajax
3. In the success function of the Ajax request in step 2 combine the two datasets
4. Still in the success function initialize Datatables with the dataset using the data

Kevin

[Deleted User]

Hi Kevin,

I'm not having much luck in working out how to use jQuery Ajax...

function getData() {
    $.ajax({
        url : 'https://opendata.arcgis.com/datasets/1.geojson',
        type: 'GET',
        success : handleData 
    })
}

function handleData(data) {
    alert(data);
    function getData2() {
    $.ajax({
        url : 'https://opendata.arcgis.com/datasets/2.geojson',
        type: 'GET',
        success : combineData
    })
}
}


function combineData(data) {
    alert(data);
    //do some stuff
}

This json.concat thing looked like an option which I found online.

var json1 = [{id:1, AcquisitionId: 'https://opendata.arcgis.com/datasets/1.geojson'}]
var json2 = [{id:1, AcquisitionId: 'https://opendata.arcgis.com/datasets/2.geojson'}]
var finalObj = json1.concat(json2);

$(document).ready(function() {
    $('#example1').DataTable( {
        ajax: finalObj,
        columns: [
            { "data": "properties.AcquisitionId"},
            { "data": "properties.ArchiveId" },
            { "data": "properties.SurveyDataTypes" },
            { "data": "properties.Description" },
            { "data": "properties.UNCPath", "render": function ( data, type, row, meta ) {
        return '<a href="'+data+'">Download</a>';
        ]
    } );
} );

Cheers

Rocke

kthorngren

The best thing to do is to post a link to your page or a test case so we can see what is happening. If you can't do that then let us know what is happening, any errors and where the errors point to.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

It doesn't seem correct that you have defined function getData2(). I don't see where is gets called. Try removing the commented lines.

function handleData(data) {
    alert(data);
    function getData2() {. // remove
    $.ajax({
        url : 'https://opendata.arcgis.com/datasets/2.geojson',
        type: 'GET',
        success : combineData
    })
} // remove
}

This json.concat thing looked like an option which I found online.

Does that work?

Kevin

[Deleted User]

Hi Kevin,

Sorry for the delay in responding I totally forgot about this, we'll blame Covid-19...

This is what I currently have which doesn't do anything. The geojson links are current.

function getData() {
    $.ajax({
        url : 'https://opendata.arcgis.com/datasets/88323df563804bab9b7e09c90cedd525_1.geojson',
        type: 'GET',
        success : handleData
    });
};

function handleData(data) {
    alert(data);
    function getData2() {
    $.ajax({
        url : 'https://opendata.arcgis.com/datasets/88323df563804bab9b7e09c90cedd525_2.geojson',
        type: 'GET',
        success : combineData
    });
  };
};

function combineData(data) {
    alert(data);
    $(document).ready(function() {
    $('#example').DataTable( {
        ajax: data,
        columns: [
            { "data": "properties.BoreholeId"},
            { "data": "properties.BoreholeName" },
            { "data": "properties.ArchiveId" },
            { "data": "properties.Description" },
            { "data": "properties.UNCPath", "render": function ( data, type, row, meta ) {
      return '<a href="'+data+'" target="_blank">Download</a>';
    } },
    ],
    dom: 'lfrtBip',
    buttons: [
        'copy', 'excel', 'pdf', 'csv'
    ]
  });
});
};

This json.concat thing looked like an option which I found online.

As to your question.

Does that work?

No it didn't

Table format

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
      <tr>
        <th>BoreholeId</th>
        <th>BoreholeName</th>
        <th>ArchiveId</th>
        <th>Description</th>
        <th>UNCPath</th>
      </tr>
    </thead>
    <tfoot>
        <tr>
        <th>BoreholeId</th>
        <th>BoreholeName</th>
        <th>ArchiveId</th>
        <th>Description</th>
        <th>UNCPath</th>
        </tr>
        </tfoot>
    </table>

And approx. what it should look like except with BoreholeName after BoreholeId

colin

As Kevin said, a link to your page, or a test case, would help us progress this. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

[Deleted User]

Hopefully this works

http://live.datatables.net/xiwepico/1/edit?html,css,js,console,output

kthorngren

Your test case isn't calling any of the functions you defined. Please update the test case to call the functions you use to setup the Datatable.

Kevin

[Deleted User]

Your test case isn't calling any of the functions you defined. Please update the test case to call the functions you use to setup the Datatable.

Hi Kevin

And that is why I'm asking the question here, I don't know how to setup the functions to call both Geojson files and then combine them into one DT.

This code works with one of the files, it doesn't work on your test site though?

$(document).ready(function() {
$('#example').DataTable( {
    "serverside": true,
    "deferRender": true,
    "ajax":{
        "url":"https://opendata.arcgis.com/datasets/88323df563804bab9b7e09c90cedd525_2.geojson",
        "dataSrc": "features"
    },
    "columns": [
      { "data": "properties.BoreholeId"},
      { "data": "properties.ArchiveId" },
      { "data": "properties.Description" },
      { "data": "properties.UNCPath", "render": function ( data, type, row, meta ) {
      return '<a href="'+data+'" target="_blank">Download</a>';
    } },
    ],
    dom: 'lfrtBip',
    buttons: [
        'copy', 'excel', 'pdf', 'csv'
    ]
  });
});

kthorngren

I made a couple changes to your test case:
http://live.datatables.net/xiwepico/3/edit

I commented out some lines. Use data to load the data instead of ajax. ajax expects a URL.

You might need to chagne the process or URLs used for the data as it looks like the response from the url in handleData() doesn't contain properties.BoreholeNam.

I added this to call handleData():

 $(document).ready( function () {
  handleData();
} );

Hope this gets you started.

Kevin

[Deleted User]

Thanks Kevin, I'll take a look tonight.

The first geojson file from the 3 function attempt further up has BoreholeName.

https://opendata.arcgis.com/datasets/88323df563804bab9b7e09c90cedd525_1.geojson