Having issues creating dimension from JSON in Looker

Question

TL;DR

• Is there a way to define key and values as dimensions and to use those dimensions to query a table holding JSON data in Looker?

---

I've been trying to work with JSON in Looker (Not Looker Studio) and haven't had much success. The basic problem is:

We have a questionnaire feature, and we capture the questions and answers to all questionnaires (regardless of type) in a single table, which is one row per answer. The table looks something like this:

id	question type	question	answer
1	personal	q1	a1
1	personal	q2	a3
2	business	q3	a5
3	personal	q1	a2
3	personal	q2	a4

This table works just fine in BQ when I need to find out a particular questionnaire with exactly two question and answer pairs, but in Looker I have not been able to create an Explore in a way that allows the end user to do that. Currently, the solution we use is to clean up the data by creating another table where we define questions as columns and answers as entries (rows) but that requires too much data engineering/warehousing resource. The move to JSON would create a single row per questionnaire, so we could search that single entity and get the expected results by using something like a:

WHERE JSON_VALUE(json_questionnaire, '$.q1') = a1
      AND JSON_VALUE(json_questionnaire, '$.q2') = a3

Whereas a:

WHERE question IN ('q1','q2')
      AND answer IN ('a1','a3')

Will return all questionnaires with just one combo of question and answer pairs. The second option is very easy to do in Looker with the current table but doesn't yield the correct results as explained.

So finally, the question is, has anyone worked with JSON in Looker? Is there a way to define key and values as dimensions and to use those dimensions to query a table holding JSON data in Looker?

If neither are possible, does anyone have any suggestions on how to record different questionnaires in a table in a way that's flexible enough that you don't have defined columns/pivots for new questionnaires, but still gives meaningful insights when queried?

Tried to create a question ordinal column so that we'd store different questionnaires first column in the question1 column, so the above would look something like:

id	question_type	question1	answer1	question2	answer 2
1	question_type	q1	a1	q2	a3
2	question_type	q3	a5	null	null
3	question_type	q1	a2	q2	a4

The main issue is that the end user would have to understand that the first question in the questionnaire is the question in question1 so there's a chance the end user would get the wrong results by querying the wrong column.

Tried using JSON but having issues creating dimensions in Looker that can then be used in an explore to query the table.