sqlgoogle-cloud-platformgoogle-bigquerymetadata

Fetch columns that match query criteria


I need to get a columns (probably their names and data) from the table given the query criteria.

Query could have projection with many fields, but I need to understand for every returned row why that row was matched, i.e. data in which columns match the criteria.

A bit of example, let's say there is a table Planets, see below

Id Planet Star Type
1 Earth Sun Alpha
2 Saturn Sun Beta
3 Venus Orion Gamma
4 Mars Aldebaran Alpha
5 Alpha Alpha Alpha

The query like

SELECT * FROM Planets WHERE Planet = 'Earth' OR Type = 'Alpha'

should return three rows, such resulting rows already stored in some intermediate view like FilteredPlanets.

I'm interested in the fact that in the first row from the table above the Planet & the Type column did the job of matching, for the row with id 4 the Type column worked and for the row with id 5 all except for Id column worked. In other words, having table with filtered results and the original query, I need to find the list of columns with matched data for each row.

NB. The data are in google big query, but I'm playing with SQL for now, mostly with CASE statement to achieve the goal. Not sure if BigQuery can run some kind of server-side job to gather needed result.

Bonus: for query like

SELECT * FROM Planets WHERE Planet = 'Earth' OR Planet like '%rth'

Would be super to understand that column Planet get hit twice for the row id#1 of original table (both criteria worked with column Planet).


Solution

  • I think based on my understanding of your question, you require the column names which hit the condition, let me know if that is correct.

    for your first query , you can try a few combination of CASE statements to get the combination required

    Fiddle : Syntax is in Postgres as I dont have Big query, so I was unable to test.

    SELECT
      Id,
      Planet,
      Star,
      Type,
      
      CONCAT(
        CASE WHEN Planet = 'Earth' THEN 'Planet' ELSE NULL END,
        CASE WHEN Planet = 'Earth' AND Type = 'Alpha' THEN ', ' ELSE '' END,
        CASE WHEN Type = 'Alpha' THEN 'Type' ELSE NULL END
      ) AS Matched_Columns
    
    FROM Planets
    WHERE Planet = 'Earth' OR Type = 'Alpha';
    

    Output

    enter image description here

    Similarly for second query

    SELECT
      Id,
      Planet,
      Star,
      Type,
    
      CONCAT(
        CASE WHEN Planet = 'Earth' THEN 'Planet' ELSE NULL END,
        CASE 
          WHEN Planet = 'Earth' AND Planet LIKE '%rth' THEN ', Planet' 
          ELSE '' 
        END,
        CASE WHEN Planet LIKE '%rth' AND Planet != 'Earth' THEN 'Planet' ELSE NULL END
      ) AS Matched_Columns
    
    FROM Planets
    WHERE Planet = 'Earth' OR Planet LIKE '%rth';
    

    Output

    enter image description here