0

Trying to figure out the best way to query a MySQL table containing a json column.

I am successfully able to get product OR port.

SELECT ip, JSON_EXTRACT(json_data, '$.data[*].product' ) FROM `network`

This will return:

["ftp","ssh"]

What I'm looking to get is something like this, or some other way to represent association and handle null values:

[["ftp",21],["ssh",22],[NULL,23]]

Sample JSON

{
 "key1":"Value",
 "key2":"Value",
 "key3":"Value",
 "data": [
      {
       "product":"ftp",
       "port":"21"
      },
      {
       "product":"ssh",
       "port":"22"
      },
      {
       "port":"23"
      }
     ]
}
Onitsoga
  • 37
  • 7
  • [JSON_TYPE](https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-type)... Possible duplicate: https://stackoverflow.com/questions/41171636/cant-detect-null-value-from-json-extract?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Bitcoin Murderous Maniac Apr 16 '18 at 22:46
  • This would potential only cover the null values portion of my question. But thanks! – Onitsoga Apr 16 '18 at 23:27

0 Answers0