I am trying to create a SQL query which involves scripted fields from Jira, but there is no information available for scripted fields in the customfieldvalue table.
Apparently it is not stored in the same table or is not there in the database at all.
Reading few old similar discussions, I found that the scripted fields work over indexes, can anybody suggest a workaround to fetch values of scripted fields via database if possible?
There is no way to get scripted/calculated fields from the database (yet another reason to not use the database - it is the worst possible way to get information from Jira). The scripted fields simply aren't there. The best you could do with the database is read the scripts behind the fields and replicate the logic in them from scratch to calculate them for yourself.
You will need to move to using the UI or REST API - they can expose the index and hence scripted fields.
I don't know, it's not something we should ever be worrying about. I would expect it to be a table named AO_4B00E6_something, but I've not looked.
You do know that to extract this, you're going to need to implement a full Groovy/Java environment to run the scripts in against the data you extract?
This is not an insignificant piece of work, you could be trying to do this for months.
I still very strongly recommend that you stop using SQL and use REST