0

Answers to a questionnaire are stored in json in a column in this table. I need to pull out the answer to a specific question stored within this column, but am struggling to do so.

I want to get the postValue '123456789' as 'Incorporation Number'

I am not sure how to get the specific child element value - any help much appreciated.

select json_extract(table.json_column, '$.user_data.element.postValue') as 'json' 
from table
where table.id = 123

the json looks something like the below:

{
   "risk_data":[
      
   ],
   "user_data":[

       "elements":[
            {
               "postName":"postNameInput",
               "postValue":"TEST COMPANY"
            }
         ],
         "language":"english",
         "elementId":"100",
         "sectionId":"9",
         "weightage":null,
         "isHyperLink":0,
         "elementIndex":0,
         "elementTitle":{
            "english":"Company's Full Legal Name"
         },
         "hyperLinkUrl":null,
         "isWorkFlowNa":0,
         "sectionIndex":0,
         "sectionTitle":{
            "english":"Basic Company Information"
         },
         "hyperLinkText":null,
         "isMultiSelect":0,
         "selectedChart":null,
         "dataAttributes":null,
         "elementTitleAbb":{
            "english":"Company's Full Legal Name"
         },
         "isHiddenElement":0,
         "isHiddenSection":0,
         "elementInputType":"input",
         "elementFooterText":null,
         "elementHeaderText":null,
         "elementDescription":null,
         "hyperLinkTextAfter":null,
         "question_abb_table":null,
         "hyperLinkTextBefore":null,
         "enableSelfReportingForManageThirdParty":0,
         "enableSelfReportingForThirdPartyProfile":0
      }, {
         "elements":[
            {
               "postName":"postNameInput",
               "postValue":"123456789"
            }
         ],
         "language":"english",
         "elementId":"101",
         "sectionId":"10",
         "weightage":null,
         "isHyperLink":0,
         "elementIndex":4,
         "elementTitle":{
            "english":"Incorporation Number"
         },
         "hyperLinkUrl":null,
         "isWorkFlowNa":0,
         "sectionIndex":3,
         "sectionTitle":{
            "english":"Organisational Structure"
         },
         "hyperLinkText":null,
         "isMultiSelect":0,
         "selectedChart":null,
         "dataAttributes":null,
         "elementTitleAbb":{
            "english":"Incorporation Number"
         },
         "isHiddenElement":0,
         "isHiddenSection":0,
         "elementInputType":"input",
         "elementFooterText":null,
         "elementHeaderText":null,
         "elementDescription":null,
         "hyperLinkTextAfter":null,
         "question_abb_table":null,
         "hyperLinkTextBefore":null,
         "enableSelfReportingForManageThirdParty":0,
         "enableSelfReportingForThirdPartyProfile":0
      },
Barmar
  • 741,623
  • 53
  • 500
  • 612
SpruceMoose
  • 9
  • 1
    `user_data` and `elements` are arrays, you need to index them with `[something]` – Barmar Nov 23 '22 at 16:52
  • 1
    `$.user_data[1].elements[0].postValue` – Barmar Nov 23 '22 at 16:53
  • thanks @Barmar I've tried running using that syntax but still not getting any data out even shifting around the array index to try and find any other data. something like: select id, json_extract(table.form_data, '$.user_data[0].elements[11].postValue') as 'json', from table where id = 123; – SpruceMoose Nov 23 '22 at 17:41
  • Works here: https://www.db-fiddle.com/f/qnLYdkzeZ5jXSDHsZW44z5/0 – Barmar Nov 23 '22 at 17:48
  • Hmmm I am not sure then is there a minimum version of mysql that json_extract works with? Would expect a syntax error though if that was the case. – SpruceMoose Nov 24 '22 at 09:46
  • JSON functions were added in 5.7 I believe. – Barmar Nov 24 '22 at 09:48
  • I've got it the syntax was correct but I was misunderstanding the larger JSON file as to which elements were in which array. Thanks for the help – SpruceMoose Nov 24 '22 at 13:10

0 Answers0