How to LEFT JOIN two external JSON url

Question

I'm trying to accomplish below result using external JSON. I searched on google and stackverflow and i couldn't find any answer. Can anyone help me on this? Thank you in advance.

people = 
[{id: 1, name: "Tom", carid: 1},
{id: 2, name: "Bob", carid: 1},
{id: 3, name: "Sir Benjamin Rogan-Josh IV", carid: 2}];

cars=
[{id: 1, name: "Ford Fiesta", color: "blue"},
{id: 2, name: "Ferrari", color: "red"},
{id: 3, name: "Rover 25", color: "Sunset Melting Yellow with hints of yellow"}];

var res = alasql('SELECT people.name AS person_name, cars.name, cars.color \
    FROM ? people LEFT JOIN ? cars ON people.carid = cars.id',[people, cars]);

Result:

[{"person_name":"Tom","name":"Ford Fiesta","color":"blue"},{"person_name":"Bob","name":"Ford Fiesta","color":"blue"},{"person_name":"Sir Benjamin Rogan-Josh IV","name":"Ferrari","color":"red"}]

This is my code to access external JSON:

people.json

[{id: 1, name: "Tom", carid: 1},{id: 2, name: "Bob", carid: 1}, {id: 3, name: "Sir Benjamin Rogan-Josh IV", carid: 2}]

cars.json

[{id: 1, name: "Ford Fiesta", color: "blue"},{id: 2, name: "Ferrari", color: "red"},{id: 3, name: "Rover 25", color: "Sunset Melting Yellow with hints of yellow"}]

JS Code

$(document).ready(function() { 
         $.getJSON('http://example.com/people.json', function(data) { 
                    people = JSON.parse(data);
                }); 

         $.getJSON('http://example.com/cars.json', function(data) { 
                    cars = JSON.parse(data);
                }); 


        var res = alasql('SELECT people.name AS person_name, cars.name, cars.color \
        FROM ? people LEFT JOIN ? cars ON people.carid = cars.id',[people, cars]);

        document.getElementById('id01').textContent = JSON.stringify(res);
}):

This is the error I'm getting in the console

Uncaught SyntaxError: Unexpected token o in JSON at position 1
at JSON.parse (<anonymous>)
at Object.success (list.html:26)
at c (jquery.min.js:2)
at Object.fireWith [as resolveWith] (jquery.min.js:2)
at l (jquery.min.js:2)
at XMLHttpRequest.<anonymous> (jquery.min.js:2)

What exactly does not work in the code you posted? Does `alasql` throw an error? — Bergi, Apr 04 '20 at 18:19
The code is working fine but I don't know how to access the JSON using API url. — James, Apr 04 '20 at 18:21
What do you mean by "using API url"? You already have two literals in your code. — Bergi, Apr 04 '20 at 18:27
I've edited my question for better understand what I'm trying to accomplish. — James, Apr 04 '20 at 18:58
Ah, with that code snippet now the question makes sense. The problem is explained in https://stackoverflow.com/q/23667086/1048572 — Bergi, Apr 04 '20 at 19:14

score 0 · Answer 1 · answered Apr 04 '20 at 18:10

0

Just iterate over each person and find the corresponding car details, In this way you can accomplish your task.

Please find below working code with simple for of. You can also use map operator as well

people = [{
    id: 1,
    name: "Tom",
    carid: 1
  },
  {
    id: 2,
    name: "Bob",
    carid: 1
  },
  {
    id: 3,
    name: "Sir Benjamin Rogan-Josh IV",
    carid: 2
  }
];

cars = [{
    id: 1,
    name: "Ford Fiesta",
    color: "blue"
  },
  {
    id: 2,
    name: "Ferrari",
    color: "red"
  },
  {
    id: 3,
    name: "Rover 25",
    color: "Sunset Melting Yellow with hints of yellow"
  }
];

const result = [];
for (const peop of people) {
  const car = cars.filter(d => d.id === peop.carid)[0];
  result.push({
    person_name: peop.name,
    name: car.name,
    color: car.color
  });
}

console.log(result)

answered Apr 04 '20 at 18:10

Jasdeep Singh

7,901
1
11
28

My trouble is how do I access external JSON objects in this solution. First JSON Url: https://externaldomain.com/json/people.json Second JSON Url: https://externaldomain.com/json/cars.json – James Apr 04 '20 at 18:13
what do you mean by extenal JSON object here ? Isn't the output of my solution is required output ? – Jasdeep Singh Apr 04 '20 at 18:15
I am not able to open the URL's shared by you – Jasdeep Singh Apr 04 '20 at 18:17
That is example url but the real url will have the same JSON object in my question. – James Apr 04 '20 at 18:19

score 0 · Answer 2 · answered Apr 04 '20 at 22:22

I found the answer.

 var requestpeop = new XMLHttpRequest();
    requestpeop.open('GET', 'people.json', false);  // `false` makes the request synchronous
    requestpeop.send(null);

    if (requestpeop.status === 200) {// That's HTTP for 'ok'
      var people =  JSON.parse(requestpeop.responseText);
    }

    var requestcar = new XMLHttpRequest();
    requestcar.open('GET', 'car.json', false);  // `false` makes the request synchronous
    requestcar.send(null);

    if (requestcar.status === 200) {// That's HTTP for 'ok'
      var cars = JSON.parse(requestcar.responseText);
    }

How to LEFT JOIN two external JSON url

2 Answers2