Data field in occurrences and notes question

[Juan David Gómez]

Hello Grafeas Developers!

Working with Grafeas I noticed the Postgres implementation is storing the details of the notes/occurrences in a text field. I did the same in the Oracle implementation but I'm wondering if is correct to use JSON as the data type for that column. 

The main motivation that I'm seeing about using JSON instead of TEXT is that will provide advanced search capabilities. Currently using the field as TEXT the only filtering option is LIKE.

In other words currently, if we want to filter by all the occurrences with cvssScore = 4.3 we will need to do something similar to:

SELECT data FROM occurrences WHERE project_name = $1 AND occurrence_name = $2 AND data like '%cvssScore = 4.3%'

Using JSON as the data type for that field we could move to something similar to:

SELECT data FROM occurrences WHERE project_name = $1 AND occurrence_name = $2 AND data -> vulnerability ->> cvssScore = 4.3

I know those kinds of searches are not being implemented but looking forward to the future and the kind of information that will be valuable information to extract from the DB. 

I'm seeing only one reason for not adopting the change of data type:

- I haven't seen any good articles about the performance of a query using LIKE vs using the JSON approach

But on the other hand I one more for adopting a JSON field :

- Using LIKE queries could lead to errors, for example, if someone stores in the description of the occurrence containing the text:

"description":" A bad vulnerability with cvssScore = 4.3 " 

I know is an edge case  but I just want to highlight the risk of a query using LIKE 

What do you think?

Thanks!

Juan 

 

[Martin Hebrank]

Hello, 

I do like the idea of using JSON instead of text, even if we haven't seen any performance data. 

That's what we did in our mysql implementation of grafeas specifically so that we could have an easier time implementing filtering. We ended up doing as Vincent suggested in the filtering issue in github and using CEL structured filters that get converted to the equivalent JSON based SQL. 

If someone wants to look at it, we have it up: https://github.com/pnctech/grafeas/tree/mysql. It's not quite ready to merged into even our own master, much less see if someone wants to do a pull into grafeas itself, but it's working for basic usages. 

** Martin