Skip to content
Advertisement

Best way to ignore null values in MYSQL

My data looks like this,

enter image description here

I have multiple values in the Table field with each having its own unique values for the Data field. However, Data values for TableA will be the same format in the table myTable. Same applies to all records in the Table field.

I am using JSON_EXTRACT to get the value in conjunction with CASE statements.

SELECT
 CASE
        WHEN
            Table = 'table A'
        THEN
            CONCAT_WS('n','Name: ',
                    JSON_EXTRACT(Data, '$.name'))
    END AS 'tableA',
CASE
        WHEN
            Table = 'table B'
        THEN
            CONCAT_WS('n','Location: ',
                    JSON_EXTRACT(Data, '$.fieldType'))
    END AS 'tableB' from myTable

The problem with this is that I will be getting null values.

I am getting the below results,

enter image description here

I am expecting the below results,

enter image description here

I want to avoid the null values. Is there any other way to extract the data ? I am using MYSQL.

Advertisement

Answer

Option one:

Select * 
from ( SELECT CASE WHEN Table_c = 'table A' THEN
                        CONCAT_WS('n','Name: ', JSON_EXTRACT(data_c, '$.name'))
              END AS 'TableA'
       from test_t) A,
     ( SELECT CASE WHEN Table_c = 'table B' THEN
                        CONCAT_WS('n','Location: ', JSON_EXTRACT(data_c, '$.fieldType'))
              END AS 'TableB'
       from test_t) B
where TableA is not null 
and TableB is not null 

Option two:

SELECT CASE WHEN Table_c = 'table B' THEN
                 CONCAT_WS('n','Location: ', JSON_EXTRACT(data_c, '$.fieldType'))
            ELSE
                 CONCAT_WS('n','Name: ', JSON_EXTRACT(data_c, '$.name'))
       END AS 'Data'
       , CASE WHEN Table_c = 'table B' THEN
                 'Table B'
            ELSE
                 'Table A'
         END AS 'Table'
from test_t    

Here is the demo

Please note that I have changed the names of the table and columns in my demo.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement