title: SQL query to get different columns from a json string using a pattern matching
body: <p>I have a json string - {&quot;exteriorCheck&quot;:{&quot;criteria&quot;:[{&quot;code&quot;:&quot;EXTERIOR_BODY&quot;,&quot;title&quot;:&quot;<strong>Exterior - XYZ</strong>&quot;,&quot;value&quot;:5},{&quot;code&quot;:&quot;EXTERIOR_RIMS&quot;,&quot;title&quot;:&quot;<strong>Exterior - ABC</strong>&quot;,&quot;value&quot;:4}],&quot;images&quot;:[{&quot;code&quot;:&quot;EXTERIOR_PICTURES&quot;,&quot;keys&quot;:[&quot;share-tasks-b1c757e3-0cb6-41ea-a298-f3430aafb36c/0&quot;]}],&quot;comment&quot;:&quot;i.o &quot;},&quot;interiorCheck&quot;:{&quot;criteria&quot;:[{&quot;code&quot;:&quot;INTERIOR_SEATS&quot;,&quot;title&quot;:&quot;<strong>Interior - Seats</strong>&quot;,&quot;value&quot;:5}</p>
  <p>I want to create a column whenever there is  &quot;title&quot; like for &quot;title&quot;:&quot;Exterior - XYZ&quot;- the column would be Exterior - XYZ and the values would be taken from &quot;value&quot;:5 , so 5 will be my output. Since there multiple such cases in the string- it is difficult to use substr with position. I have tried -</p>
  <p>select
  case when &quot;json&quot;  like '%Exterior - XYZ%' then substr(&quot;JSON&quot;,89,1)
  else null end as &quot;Exterior - XYZ&quot;. But for the entire json its difficult to get the position.</p>
  <p>Desired output:</p>
  <p>Exterior - XYZ   | Exterior - ABC | Interior - Seats</p>
  <p>5                  |       4              |           5</p>
  <p>How to proceed using AWS Athena (considering multiple string functions wont work at athena)</p>