Grooper 21.00.0082 is available as of 12-12-2023! Check the  Downloads Discussion  for the release notes and to get the latest version.
Grooper 23.00.0042 is available as of 03-22-2024! Check the Downloads Discussion for the release notes and to get the latest version.
Grooper 23.1.0018 is available as of 04-15-2024! Check the  Downloads Discussion  for the release notes and to get the latest version.
Options

SQL / JSON Query Help

[Deleted User][Deleted User] Posts: 0 ✭✭
in The Astronauts (Q&A)
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

  • Options
    [Deleted User][Deleted User] Posts: 0 ✭✭
    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

  • Options
    rmccutcheonrmccutcheon Posts: 756 ✭✭✭
    Can one of you help with this, or point him in the right direction?
  • Options
    [Deleted User][Deleted User] Posts: 0 ✭✭
    @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!
  • Options
    GrooperGuruGrooperGuru Posts: 481 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
    mharrison@bisok.com
  • Options
    [Deleted User][Deleted User] Posts: 0 ✭✭
    @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.