0

My data looks like this (table name: test_table):

{
    timestamp: 'some_timestamp',
    headers: [{'name': 'test', 'value': 'sth'}, {'name': 'test2', 'value': 'sth2'}, {'name': 'test3', 'value': 'sth3'}]
}

How can I select only {'name': 'test2', 'value': 'sth2'} dynamically, ie. where condition is: name needs to have value oftest2 and we don't know which object it is within the array.

I've tried:

SELECT JSON_SEARCH(headers, 'one', 'test2', NULL, '$[*].name') IS NOT NULL
FROM test_table
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Murakami
  • 3,474
  • 7
  • 35
  • 89
  • Why would you store this data in JSON when it makes it so difficult to query the data? Just store it in normal form, in a second table with columns `name` and `value`. Each name/value pair is stored on its own row. Then you can query `SELECT ... FROM test_table_headers WHERE name = 'test2'`. – Bill Karwin Jan 28 '21 at 15:23
  • This is a structure I cannot modify as this comes as it is from aws waf firewall logs – Murakami Jan 28 '21 at 15:45

0 Answers0