1

I get a response from a JSON API which contains the following data.

"data": [
    {
        "num1": 1,
        "num2": 2,
        "txt1": "test3"
    },
    {
        "num1": 4,
        "num2": 5,
        "txt1": "test6"
    }
]

Next, I have a database table that looks much the same.

|----------------|
|   my_table     |
|----------------|
| num1           |
| num2           |
| txt1           |
|----------------|

Now I need to get all the rows in the table that match any of the elements in the data array.

SELECT * FROM my_table AS t
LEFT JOIN array AS a
ON t.num1 = a.num1 AND t.num2 = a.num2 AND t.txt1 = a.txt1

But of course, I can't treat the array like it's a table. Or can I? How would I solve this?

By the way, I'm writing in Python and I'm using SQLObject library. But this should not make much of a difference.

Mtihc
  • 191
  • 1
  • 12
  • `INSERT` json data into database then you can use SQL query. – furas Nov 27 '15 at 12:46
  • I was hoping I could write a query in a clever way. I can't just go insert the json data into the database. Because another process could be doing the same thing at the same time. – Mtihc Nov 27 '15 at 12:52
  • I could iterate over the array. And do a simple `SELECT WHERE` for every element. But that doesn't seem efficient. – Mtihc Nov 27 '15 at 12:53
  • 1
    My first idea is to create a TEMPORARY table in the database and insert those values there, before you perform the join... This way other processes would be disturbed by it. – 576i Nov 27 '15 at 13:24
  • Or create a `:memory:` SQLite3 table and work on it. – Michel Nov 27 '15 at 13:34
  • @576i That sounds like a real solution. But it also feels kindof overkill. What is more efficient? Doing a bunch of `INSERT`s into a temporary table and then a `JOIN`. Or simply doing a bunch of `SELECT WHERE`s? – Mtihc Nov 27 '15 at 14:50
  • 1
    @Mtihc I think it all depends on the size of the data you receive by JSON. Remember that you can `INSERT` hundreds of rows into tables with one SQL command. The communication overhead of 2 queries (inserting the data and `SELECT` with `JOIN`) vs. hundreds of `SELECT`s is something to consider. I think, if you expect more than 10 sets of values from the JSON API, the `TEMPORARY TABLE` is the way to go. – Maciek Nov 27 '15 at 15:23
  • @Maciek Oh yeah, i kindof forgot I can `INSERT` many rows with one SQL command. I understand your point about the size of the data. I think I know enough to make a decision now. Thank you. – Mtihc Nov 27 '15 at 15:34

0 Answers0