SQL / JSON Query Help

RillonDodgers

Im experimenting with trying to get the confidence levels from a page (Grooper.BatchPage under TypeName in the TreeNode table) and the JSON information in NodeValues has the stuff I want, but I'm having an Issue with the query trying to display the information. I don't have proficient knowledge in SQL or JSON to know how exactly this is setup so I'm requesting some assistance in finding a way to display the information I need.

Thanks!

Query:

SELECT

JSON_VALUE(NodeValues,'$.Value.ContentTypeId')

FROM TreeNode

WHERE Id LIKE 'ee1252f1-9446-4baf-a070-27cf85cad4c7'

JSON: 

[{"Key":"Grooper.Classify.Candidates","Value":"[{\"ContentTypeId\":\"95938dde-04b7-4933-8708-f3d196441dde\",\"_Similarity\":0.67112937589413035},{\"ContentTypeId\":\"34f21552-d526-455f-ae8c-c1b888b22669\",\"_Similarity\":0.61695381514255643},{\"ContentTypeId\":\"a78b75f9-ee1d-4993-8707-1061efc9a57e\",\"_Similarity\":0.52725982996502474},{\"ContentTypeId\":\"da017340-aecd-4565-abfa-6d09881488fc\",\"_Similarity\":0.51776331954986543},{\"ContentTypeId\":\"408d133a-e5b8-4b42-a4d9-28d0eaf9f36b\",\"_Similarity\":0.5153945108555219},{\"ContentTypeId\":\"29f51b70-fc49-4873-8a60-57845fceac26\",\"_Similarity\":0.51451151011459628},{\"ContentTypeId\":\"573e4aba-191b-4a40-8e2c-f9d7dc568d1e\",\"_Similarity\":0.5115822291312474},{\"ContentTypeId\":\"8867bf3c-9f73-497d-904a-7f27a07e9275\",\"_Similarity\":0.50591084688374321},{\"ContentTypeId\":\"eba0131d-7df5-497f-be36-1e2242befea2\",\"_Similarity\":0.48214942332446087},{\"ContentTypeId\":\"e5b3aedf-e7c1-4931-b8ed-25520d889bdd\",\"_Similarity\":0.4784069628471625},{\"ContentTypeId\":\"b0fd400a-eef1-43bb-95bd-9e64d50af2c5\",\"_Similarity\":0.4620778030030539},{\"ContentTypeId\":\"507d50d8-6c43-4c7d-8e38-803b50004dd8\",\"_Similarity\":0.45704062557383318},{\"ContentTypeId\":\"a8124217-8c89-4c20-a258-dbc54e77ca58\",\"_Similarity\":0.44720843100919272},{\"ContentTypeId\":\"0506c6aa-f472-4da0-b032-b3988ddfc41d\",\"_Similarity\":0.44123976001794957},{\"ContentTypeId\":\"a4216f83-4b97-4433-98da-d230f8f1b2e8\",\"_Similarity\":0.43792056817819014},{\"ContentTypeId\":\"e808aca3-81fd-4417-be2f-bb12d33066b7\",\"_Similarity\":0.43099094578234481},{\"ContentTypeId\":\"780a220c-030a-4b47-8b1e-a0686d966a79\",\"_Similarity\":0.43099094578234481},{\"ContentTypeId\":\"61759bca-ed61-4d3a-b62c-e8142e16bb70\",\"_Similarity\":0.42976275532126457},{\"ContentTypeId\":\"b5114339-d665-445f-89d2-4e0948bb95af\",\"_Similarity\":0.42254441248139779},{\"ContentTypeId\":\"2489d167-e209-4fc2-bf87-3a8d2302bfa6\",\"_Similarity\":0.42094240197880467}]"},{"Key":"Grooper.Classify.EPI","Value":"{\"OriginalText\":\"2 of 1\",\"PageNo\":2}"}]

RillonDodgers

I figured it out

select
JSON_VALUE(JSON_VALUE(t4.NodeValues, '$[0].Value'), '$[0].ContentTypeId') as content_typ_id,
JSON_VALUE(JSON_VALUE(t4.NodeValues, '$[0].Value'), '$[0]._Similarity') as similarity
from TreeNode t1
inner join TreeNode t2 on t1.Id = t2.ParentId
inner join TreeNode t3 on t2.Id = t3.ParentId
inner join TreeNode t4 on t3.Id = t4.ParentId
where t1.name = '<REDACTED>'
and t2.TypeName = 'Grooper.Core.BatchFolder'

rmccutcheon

@Brian @Bradley @Ryan @GrooperGuru

Can one of you help with this, or point him in the right direction?

RillonDodgers

@rmccutcheon this isn't a rush or anything, I was just trying to see if it was possible to get that information from the JSON field. Thanks!

GrooperGuru

Since you asked so politely over a cold stout on Friday, I thought I should find a resource for you this week. Help me understand exactly what you are trying to accomplish. I see that your goal is to obtain the confidence scores from each page. Do you need to obtain scores for all candidates, or just the top candidate? At one point in your process do you need these values, and where do you need them displayed? Is this something you're trying to just export elsewhere, or are you just wanting to build a SQL-based process outside of Grooper to obtain this data?

RillonDodgers

@GrooperGuru sorry for the late reply. I'm just trying to build a SQL-based process outside of Grooper to obtain the data. I was trying to replace the part of the JSON field where it says ContentTypeId with the actual name of the node it's referencing.