Filter NULL columns in in MySQL result

Question

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?

Barmar · Accepted Answer · 2017-01-11 23:18:52Z

1

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);

answered Jan 11, 2017 at 23:18

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3191334 Over a year ago

Thanks that answers my question. In my case I use python but the principle is the same.

Barmar Over a year ago

Oops, sorry I didn't read the tags. Maybe you should convert this to Python and post your code, so you can accept a proper answer.

Collectives™ on Stack Overflow

Filter NULL columns in in MySQL result

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related