CODEWITHSUNDEEP
pythonmysqlnull
user3191334
user3191334

Reputation: 1168

Filter NULL columns in in MySQL result

I built a small python API which should return a JSON filled with all necessary key / value pairs. The key values are just queried from a MySQL DB like:

SELECT * 
FROM flowers 
WHERE favourite = 1

Output:

id        color     size     favourite  ...
---------------------------------------------
1234      yellow    big      1          ...
1235      <null>    small    1          ...
1236      blue      <null>   1          ...
1237      <null>    <null>   1          ...

Executing this query will return an array which looks similar to this:

[
    {'id': 1234, 'color': "yellow", 'size': "big", 'favourite': 1, ... },
    {'id': 1234, 'color': "<null>", 'size': "small", 'favourite': 1, ... },
    {'id': 1234, 'color': "blue", 'size': "<null>", 'favourite': 1, ... },
    {'id': 1234, 'color': "<null>", 'size': "<null>", 'favourite': 1, ... }
]

In order to prevent sending any useless entries to the clients, I'd like to choose what I'm SELECTing. My goal is to keep selecting each row where favourite=1, but just leave away the column, returning <null>. So I get a result equal to this:

[
    {'id': 1234, 'color': "yellow", 'size': "big", 'favourite': 1, ... },
    {'id': 1234, 'size': "small", 'favourite': 1, ... },
    {'id': 1234, 'color': "blue", 'favourite': 1, ... },
    {'id': 1234, 'favourite': 1, ... }
]

I'm also very interested in the performance. Is it possible / worth to reach my target via the MySQL query or is it better to select also the <null> results and filter them with a custom python method afterwards?

Upvotes: 0

Views: 286

Answers (1)

Barmar
Barmar

Reputation: 781741

You can't filter columns in SQL, because the columns are specified in the SELECT clause, and all rows have the same columns. You'll have to do it in PHP, by looping over the columns when adding them to the result array.

$result = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $result_row = array();
    foreach ($row as $col => $value) {
        if ($value !== null) {
            $result_row[$col] = $value;
        }
    }
    $result[] = $result_row;
}
echo json_encode($result);

Upvotes: 1

Related Questions