1

Is there a way to pass a value or variable into json_path in Mysql?

I am trying to use json_table to get value from the header of my json and then get date in my content from the index that i got.

In my code below, i am trying to passe the value jsh.cName

My query

    Select jsh.cName, jsc.qteRecu from
DocumentField as df inner join DocumentAnchor DA on DA.currentDocId IN (
    Select df.documentId from
    DocumentField as df inner join DocumentAnchor DA on DA.currentDocId = df.documentId
    inner join tpl_fields TF on TF.id = df.fieldId and TF.fieldId = 135 AND df.calcValue = "13476148-0" -- $135$
) inner join tpl_fields TF on TF.id = df.fieldId and TF.fieldId = 143 AND JSON_VALID(df.calcValue)
inner join Template on Template.id = DA.templateId AND Template.TypeTemplate_id = 6 -- bordereau de livraison
CROSS JOIN JSON_TABLE(
JSON_UNQUOTE(df.calcValue), "$.header[*]"
COLUMNS(
cId INT path '$."globalColumnId"',
cName VARCHAR(256) PATH '$."name"')
) as jsh ON jsh.cId = 8
CROSS JOIN JSON_TABLE(
JSON_UNQUOTE(df.calcValue), "$.content[*]"
COLUMNS(
qteRecu VARCHAR(256) PATH "$.MY_DATE_HERE(jsh.cName)")
) as jsc
WHERE jsc.qteRecu != "" AND jsc.qteRecu IS NOT NULL;

My json, there is the header where i got the name of the key and content is where i'am trying to get the value from the key name :

"header": [
    {
        "id": 1026,
        "name": "No projet",
        "templateId": 213,
        "tpl_fieldId": 3374,
        "pageId": 0,
        "isLineDefiner": 0,
        "startValue": "",
        "mandatoryStartValue": 0,
        "stopValue": "",
        "mandatoryStopValue": 0,
        "defaultValue": "",
        "width": 50,
        "height": 238,
        "x1": 1226,
        "y1": 627,
        "x2": 1276,
        "y2": 864,
        "ocrConfTolerance": 65,
        "zoneTolerencePix": null,
        "mask": null,
        "created": "2021-06-14 15:29:39",
        "createdBy": 1,
        "modified": "2021-06-14 15:29:39",
        "modifiedBy": null,
        "disabled": 0,
        "noSpace": 0,
        "typeId": 0,
        "isDummy": 0,
        "globalColumnId": 25,
        "listDataId": null,
        "zoneTolerence": null
    },
    {
        "id": 1016,
        "name": "No ligne",
        "templateId": 213,
        "tpl_fieldId": 3374,
        "pageId": 0,
        "isLineDefiner": 1,
        "startValue": "",
        "mandatoryStartValue": 0,
        "stopValue": "",
        "mandatoryStopValue": 0,
        "defaultValue": "",
        "width": 50,
        "height": 238,
        "x1": 1278,
        "y1": 627,
        "x2": 1328,
        "y2": 864,
        "ocrConfTolerance": 65,
        "zoneTolerencePix": null,
        "mask": null,
        "created": "2021-06-09 13:44:33",
        "createdBy": 1,
        "modified": "2021-06-14 15:29:39",
        "modifiedBy": 1,
        "disabled": 0,
        "noSpace": 0,
        "typeId": 0,
        "isDummy": 0,
        "globalColumnId": 1,
        "listDataId": null,
        "zoneTolerence": null
    },
    {
        "id": 1017,
        "name": "Article",
        "templateId": 213,
        "tpl_fieldId": 3374,
        "pageId": 0,
        "isLineDefiner": 0,
        "startValue": "",
        "mandatoryStartValue": 0,
        "stopValue": "",
        "mandatoryStopValue": 0,
        "defaultValue": "",
        "width": 50,
        "height": 238,
        "x1": 1330,
        "y1": 627,
        "x2": 1380,
        "y2": 864,
        "ocrConfTolerance": 65,
        "zoneTolerencePix": null,
        "mask": null,
        "created": "2021-06-09 13:44:33",
        "createdBy": 1,
        "modified": "2021-06-14 15:29:39",
        "modifiedBy": 1,
        "disabled": 0,
        "noSpace": 0,
        "typeId": 0,
        "isDummy": 0,
        "globalColumnId": 11,
        "listDataId": null,
        "zoneTolerence": null
    },
    {
        "id": 1018,
        "name": "Qte re\u00e7ue",
        "templateId": 213,
        "tpl_fieldId": 3374,
        "pageId": 0,
        "isLineDefiner": 0,
        "startValue": "",
        "mandatoryStartValue": 0,
        "stopValue": "",
        "mandatoryStopValue": 0,
        "defaultValue": "",
        "width": 50,
        "height": 238,
        "x1": 1382,
        "y1": 627,
        "x2": 1432,
        "y2": 864,
        "ocrConfTolerance": 65,
        "zoneTolerencePix": null,
        "mask": null,
        "created": "2021-06-09 13:44:33",
        "createdBy": 1,
        "modified": "2021-06-14 15:29:39",
        "modifiedBy": 1,
        "disabled": 0,
        "noSpace": 0,
        "typeId": 0,
        "isDummy": 0,
        "globalColumnId": 8,
        "listDataId": null,
        "zoneTolerence": null
    },
    {
        "id": 1019,
        "name": "Prix PO",
        "templateId": 213,
        "tpl_fieldId": 3374,
        "pageId": 0,
        "isLineDefiner": 0,
        "startValue": "",
        "mandatoryStartValue": 0,
        "stopValue": "",
        "mandatoryStopValue": 0,
        "defaultValue": "",
        "width": 50,
        "height": 238,
        "x1": 1434,
        "y1": 627,
        "x2": 1484,
        "y2": 864,
        "ocrConfTolerance": 65,
        "zoneTolerencePix": null,
        "mask": null,
        "created": "2021-06-09 13:44:33",
        "createdBy": 1,
        "modified": "2021-06-14 15:29:39",
        "modifiedBy": 1,
        "disabled": 0,
        "noSpace": 0,
        "typeId": 3,
        "isDummy": 0,
        "globalColumnId": 30,
        "listDataId": null,
        "zoneTolerence": null
    },
    {
        "id": 1020,
        "name": "Compte GL PO",
        "templateId": 213,
        "tpl_fieldId": 3374,
        "pageId": 0,
        "isLineDefiner": 0,
        "startValue": "",
        "mandatoryStartValue": 0,
        "stopValue": "",
        "mandatoryStopValue": 0,
        "defaultValue": "",
        "width": 117,
        "height": 238,
        "x1": 1486,
        "y1": 627,
        "x2": 1603,
        "y2": 864,
        "ocrConfTolerance": 65,
        "zoneTolerencePix": null,
        "mask": null,
        "created": "2021-06-09 13:44:33",
        "createdBy": 1,
        "modified": "2021-06-14 15:29:39",
        "modifiedBy": 1,
        "disabled": 0,
        "noSpace": 0,
        "typeId": 0,
        "isDummy": 0,
        "globalColumnId": 33,
        "listDataId": null,
        "zoneTolerence": null
    }
],
"content": [
    {
        "No projet": "0",
        "No ligne": 1,
        "Article": "",
        "Qte re\u00e7ue": "",
        "Prix PO": 0,
        "Compte GL PO": ""
    },
    {
        "No projet": "15CALL",
        "No ligne": 2,
        "Article": "275019734",
        "Qte re\u00e7ue": 1,
        "Prix PO": 20,
        "Compte GL PO": "570010;570010;110499"
    },
    {
        "No projet": "15CALL",
        "No ligne": 3,
        "Article": "217041984",
        "Qte re\u00e7ue": 1,
        "Prix PO": 135,
        "Compte GL PO": "570010;570010;110499"
    },
    {
        "No projet": "0",
        "No ligne": 4,
        "Article": "",
        "Qte re\u00e7ue": "",
        "Prix PO": 0,
        "Compte GL PO": ""
    }
]
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Dannick Bedard
  • 373
  • 3
  • 14

1 Answers1

1

I don't think that's possible. The PATH must be fixed before you join to other tables. So there's no way to make the PATH different based on values in other tables you join to.

This is similar to questions where folks want to join to a different table or a different column per row. SQL does not support that. The tables and columns must be fixed before the query begins reading any rows of data or evaluating expressions.

Instead, I recommend to expose all the content fields using separate paths, and then use a CASE expression to pick one:

SELECT jsh.cName, CASE jsh.cName
    WHEN 'No projet' THEN jsc.NoProjet
    WHEN 'No ligne' THEN jsc.NoLigne
    WHEN 'Article' THEN jsc.Article
    ...
    END AS qteRecu
FROM ...
CROSS JOIN JSON_TABLE(
  JSON_UNQUOTE(df.calcValue), "$.content[*]"
    COLUMNS(
      NoProjet VARCHAR(256) PATH '$."No projet"',
      NoLigne INT PATH '$."No ligne"',
      Article VARCHAR(256) PATH '$.Article',
      QteRecu VARCHAR(256) PATH '$."Qte re\u00e7ue"',
      ...)
) as jsc

Another alternative is to structure your content as a different JSON document.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828