Grooper 2.90.0063 is available as of 3-24-2021! Check the  Downloads Discussion  for the release notes and to get the latest version.
Grooper 22.00.0011 is available as of 9-19-2022! Check the  Downloads Discussion  for information on new features, and to download the latest build!
Grooper 21.00.0055 is available as of 9-22-2022! Check the  Downloads Discussion  for the release notes and to get the latest version.

SQL / JSON Query Help

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}"}]

Best Answer

  • RillonDodgersRillonDodgers Posts: 25 ✭✭
    Answer ✓
    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'

Answers

  • rmccutcheonrmccutcheon Posts: 756 ✭✭✭
    Can one of you help with this, or point him in the right direction?
  • RillonDodgersRillonDodgers Posts: 25 ✭✭
    @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!
  • GrooperGuruGrooperGuru Posts: 476 admin
    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?
    Matt Harrison
    Product Manager
    [email protected]
  • RillonDodgersRillonDodgers Posts: 25 ✭✭
    @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. 
Sign In or Register to comment.