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