Grooper 2.80.0043 is now available! Check the  Downloads Discussion for the release notes and to get the latest version.
Grooper 2.90.0051 is now available! Check the  Downloads Discussion  for the release notes and to get the latest version.
The next version of Grooper - Grooper 2021 - will be entering beta soon! If you want to get a head start on some of our exciting new features, check out the  article over Smart PDFs  on the Grooper Wiki!

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.



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


[{"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 ✭✭
    Accepted Answer
    I figured it out

    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 = '<REDACTED>'
    and t2.TypeName = 'Grooper.Core.BatchFolder'


  • rmccutcheonrmccutcheon Posts: 756 mod
    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: 465 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
    Director of Strategy
    [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.