Cypher request to Generate a nested recursive json

Question

So far I have created the following nodes:

CREATE
  (b:Brand {identifier: 'ANTIINFECTIEUX',brand:'ANTIINFECTIEUX',description :'ANTIINFECTIEUX' }),
    (b)<-[:IS_A_CATALOG_BELONGING_TO]-(c:Catalog {identifier: 'ANTIBACTERIENS',catalogName:'ANTIBACTERIENS',brand:'ANTIINFECTIEUX',brandIdentifier:'ANTIINFECTIEUX',description :'ANTIBACTERIENS'}),
      (c)<-[:IS_A_PRODUCT_BELONGING_TO_THAT_CATALOG]-(p:Product {identifier: 'Amikacine',productName:'Amikacine',brand:'ANTIINFECTIEUX',brandIdentifier:'ANTIINFECTIEUX',catalog:'ANTIBACTERIENS',catalogIdentifier:'ANTIBACTERIENS',description :'Amikacine',DCI:'Amikacine', Dosage:'1g', Forme:'Inj', Case:'false', Poste:'false', CTRE:'false', CHR:'true', CHN:'true', Reference:'Amiklin', price:'200.75', stock:'500',NumeroDeLot:'87',CodeBarre:'87878787878787878',QuantiteDemandee:'50'}),
        (p)-[:IS_A_PRODUCT_BELONGING_TO_THAT_BRAND]->(b),

        (c)<-[:IS_A_PRODUCT_BELONGING_TO_THAT_CATALOG]-(p2:Product {identifier: 'Amoxicilline',productName:'Amoxicilline',brand:'ANTIINFECTIEUX',brandIdentifier:'ANTIINFECTIEUX',catalog:'ANTIBACTERIENS',catalogIdentifier:'ANTIBACTERIENS',description :'Amoxicilline',DCI:'Amoxicilline', Dosage:'500mg', Forme:'Gélules', Case:'false', Poste:'true', CTRE:'true', CHR:'true', CHN:'true', Reference:'Clamoxyl', price:'250.75', stock:'700',NumeroDeLot:'8777',CodeBarre:'998898979797',QuantiteDemandee:'50'}),
        (p2)-[:IS_A_PRODUCT_BELONGING_TO_THAT_BRAND]->(b),


  (b1:Brand {identifier: 'ANESTHESIQUES',brand:'ANESTHESIQUES',description :'ANESTHESIQUES' }),
    (b1)<-[:IS_A_CATALOG_BELONGING_TO]-(c1:Catalog {identifier: 'Anesthesiques_Generaux_et_Gaz_medicaux',catalogName:'Anesthesiques_Generaux_et_Gaz_medicaux',brand:'ANESTHESIQUES',brandIdentifier:'ANESTHESIQUES',description :'Anesthésiques généraux et Gaz médicaux'}),
      (c1)<-[:IS_A_PRODUCT_BELONGING_TO_THAT_CATALOG]-(p1:Product {identifier: 'Kétamine',productName:'Kétamine',brand:'ANESTHESIQUES',brandIdentifier:'ANESTHESIQUES',catalog:'Anesthesiques_Generaux_et_Gaz_medicaux',catalogIdentifier:'Anesthesiques_Generaux_et_Gaz_medicaux',description :'Kétamine',DCI:'Kétamine', Dosage:'50mg/amp', Forme:'Inj', Case:'false', Poste:'false', CTRE:'true', CHR:'true', CHN:'true', Reference:'Kétalar', price:'900.75', stock:'300',NumeroDeLot:'677',CodeBarre:'5454578788',QuantiteDemandee:'10'}),
        (p1)-[:IS_A_PRODUCT_BELONGING_TO_THAT_BRAND]->(b1)

The purpose is to get a nested json like this:

{ 
   "menuItems":[ 
      { 
         "name":"Anesthesiques_Generaux_et_Gaz_medicaux",
         "children":[ 
            { 
               "name":"ANESTHESIQUES",
               "children":[ 
                  { 
                     "name":"Kétamine",
                     "ProductCHR":"true",
                     "ProductForme":"Inj",
                     "ProductCHN":"true",
                     "ProductReference":"Kétalar",
                     "ProductDCI":"Kétamine",
                     "ProductCase":"false",
                     "ProductDosage":"50mg/amp",
                     "ProductIdentifier":"Kétamine",
                     "ProductPoste":"false",
                     "ProductPrice":"900.75",
                     "ProductCTRE":"true",
                     "ProductStock":"300"
                  }
               ]
            }
         ]
      },
      { 
         "name":"ANTIBACTERIENS",
         "children":[ 
            { 
               "name":"ANTIINFECTIEUX",
               "children":[ 
                  { 
                     "name":"Amikacine",
                     "ProductCHR":"true",
                     "ProductForme":"Inj",
                     "ProductCHN":"true",
                     "ProductReference":"Amiklin",
                     "ProductDCI":"Amikacine",
                     "ProductCase":"false",
                     "ProductDosage":"1g",
                     "ProductIdentifier":"Amikacine",
                     "ProductPoste":"false",
                     "ProductPrice":"200.75",
                     "ProductCTRE":"false",
                     "ProductStock":"500"
                  },
                  { 
                     "name":"Amoxicilline",
                     "ProductCHR":"true",
                     "ProductForme":"Gélules",
                     "ProductCHN":"true",
                     "ProductReference":"Clamoxyl",
                     "ProductDCI":"Amoxicilline",
                     "ProductCase":"false",
                     "ProductDosage":"500mg",
                     "ProductIdentifier":"Amoxicilline",
                     "ProductPoste":"true",
                     "ProductPrice":"250.75",
                     "ProductCTRE":"true",
                     "ProductStock":"700"
                  }
               ]
            }
         ]
      }
   ]
}

To generate that JSON file I've made the following cypher request:

MATCH (Brand:Brand)
OPTIONAL MATCH (Brand)<-[:IS_A_CATALOG_BELONGING_TO]-(Catalog:Catalog)
OPTIONAL MATCH (Catalog)<-[:IS_A_PRODUCT_BELONGING_TO_THAT_CATALOG]-(Product:Product)
OPTIONAL MATCH (Product)-[:IS_A_PRODUCT_BELONGING_TO_THAT_BRAND]->(Brand)

WITH Brand, Catalog, Product 
     ORDER BY Product.identifier ASC
WITH Brand, Catalog, 
     collect({name: Product.DCI, ProductIdentifier:Product.identifier,ProductDCI:Product.DCI,
     ProductDosage:Product.Dosage,ProductForme:Product.Forme,ProductCase:Product.Case,
     ProductPoste:Product.Poste,ProductCTRE:Product.CTRE,ProductCHR:Product.CHR,ProductCHN:Product.CHN,
     ProductReference:Product.Reference,ProductPrice:Product.price,ProductStock:Product.stock,
     NumeroDeLot:Product.NumeroDeLot,CodeBarre:Product.CodeBarre,QuantiteDemandee:Product.QuantiteDemandee}) AS pNames 
     ORDER BY Catalog.identifier ASC

WITH Brand.identifier AS name, 
     collect(Catalog.identifier ) AS cname,
    collect( pNames) AS children
     ORDER BY name ASC
RETURN apoc.convert.toJson({name:name,cname:cname,children:children})

That request holds all infos need but Unfortunately, the ideal output form it doesn't match my expectations (the resultant json that I really want) as you can see here.

"{
"children":[
             [
                 {
                   "NumeroDeLot":"677",
                   "ProductReference":"Kétalar",
                   "ProductCase":"false",
                   "ProductPrice":"900.75",
                   "ProductCTRE":"true",
                   "QuantiteDemandee":"10",
                   "ProductCHR":"true",
                   "ProductForme":"Inj",
                   "ProductCHN":"true",
                   "ProductDCI":"Kétamine",
                   "ProductDosage":"50mg/amp",
                   "ProductIdentifier":"Kétamine",
                   "name":"Kétamine",
                   "ProductPoste":"false",
                   "CodeBarre":"5454578788",
                   "ProductStock":"300"}
              ]
            ],

            "name":"ANESTHESIQUES",
            "cname":["Anesthesiques_Generaux_et_Gaz_medicaux"]
}
"

"{
     "children":[
                   [
                       {
                         "NumeroDeLot":"87",
                         "ProductReference":"Amiklin",
                         "ProductCase":"false",
                         "ProductPrice":"200.75",
                         "ProductCTRE":"false",
                         "QuantiteDemandee":"50",
                         "ProductCHR":"true",
                         "ProductForme":"Inj",
                         "ProductCHN":"true",
                         "ProductDCI":"Amikacine",
                         "ProductDosage":"1g",
                         "ProductIdentifier":"Amikacine",
                         "name":"Amikacine",
                         "ProductPoste":"false",
                         "CodeBarre":"87878787878787878",
                         "ProductStock":"500"
                        },
                        {
                          "NumeroDeLot":"8777",
                          "ProductReference":"Clamoxyl",
                          "ProductCase":"false",
                          "ProductPrice":"250.75",
                          "ProductCTRE":"true",
                          "QuantiteDemandee":"50",
                          "ProductCHR":"true",
                          "ProductForme":"Gélules",
                          "ProductCHN":"true",
                          "ProductDCI":"Amoxicilline",
                          "ProductDosage":"500mg",
                          "ProductIdentifier":"Amoxicilline",
                          "name":"Amoxicilline",
                          "ProductPoste":"true",
                          "CodeBarre":"998898979797",
                          "ProductStock":"700"
                        }
                    ]
                ],
                "name":"ANTIINFECTIEUX",
                "cname":["ANTIBACTERIENS"]
}"

It would be great if someone could help me to achieve that purpose.

{ 
   "menuItems":[ 

{
   "name":"Brand Name1",
   "children":[
               {
                  "name":"Catalog Name",
                   "children":[
                        {
                         "name": "productName1",
                          .....................
                          etc

                        },
                        {
                         "name": "productName2"
                          .....................
                        },
                        ........................
                        {
                         "name": "productNameN"
                          .....................
                        }
                   ]
               },
               ......................................
               {
                  "name":"Catalog NameN",
                   "children":[
                        {
                         "name": "productName1ForCatalogNameN",
                          .....................
                          etc

                        },
                        {
                         "name": "productName2ForCatalogNameN"
                          .....................
                        },
                        ........................
                        {
                         "name": "productNameNForCatalogNameN"
                          .....................
                        }
                   ]
               },
    ]

},
....................................................................
{
   "name":"Brand NameN",
   "children":[
               {
                  "name":"Catalog Name",
                   "children":[
                        {
                         "name": "productName1",
                          .....................
                          etc

                        },
                        {
                         "name": "productName2"
                          .....................
                        },
                        ........................
                        {
                         "name": "productNameN"
                          .....................
                        }
                   ]
               },
               ......................................
               {
                  "name":"Catalog NameN",
                   "children":[
                        {
                         "name": "productName1ForCatalogNameN",
                          .....................
                          etc

                        },
                        {
                         "name": "productName2ForCatalogNameN"
                          .....................
                        },
                        ........................
                        {
                         "name": "productNameNForCatalogNameN"
                          .....................
                        }
                   ]
               },
    ]

}

]
}

Many thanks.

score 0 · Answer 1 · answered Oct 05 '19 at 19:50

CREATE
  (b:Brand {name: 'ANTIINFECTIEUX',brand:'ANTIINFECTIEUX',description :'ANTIINFECTIEUX' }),
    (b)<-[:IS_A_CATALOG_BELONGING_TO]-(c:Catalog {name: 'ANTIBACTERIENS',catalogName:'ANTIBACTERIENS',brand:'ANTIINFECTIEUX',brandIdentifier:'ANTIINFECTIEUX',description :'ANTIBACTERIENS'}),
      (c)<-[:IS_A_PRODUCT_BELONGING_TO_THAT_CATALOG]-(p:Product {name: 'Amikacine',productName:'Amikacine',brand:'ANTIINFECTIEUX',brandIdentifier:'ANTIINFECTIEUX',catalog:'ANTIBACTERIENS',catalogIdentifier:'ANTIBACTERIENS',description :'Amikacine',DCI:'Amikacine', Dosage:'1g', Forme:'Inj', Case:'false', Poste:'false', CTRE:'false', CHR:'true', CHN:'true', Reference:'Amiklin', price:'200.75', stock:'500',NumeroDeLot:'87',CodeBarre:'87878787878787878',QuantiteDemandee:'50'}),
        (p)-[:IS_A_PRODUCT_BELONGING_TO_THAT_BRAND]->(b),

        (c)<-[:IS_A_PRODUCT_BELONGING_TO_THAT_CATALOG]-(p2:Product {name: 'Amoxicilline',productName:'Amoxicilline',brand:'ANTIINFECTIEUX',brandIdentifier:'ANTIINFECTIEUX',catalog:'ANTIBACTERIENS',catalogIdentifier:'ANTIBACTERIENS',description :'Amoxicilline',DCI:'Amoxicilline', Dosage:'500mg', Forme:'Gélules', Case:'false', Poste:'true', CTRE:'true', CHR:'true', CHN:'true', Reference:'Clamoxyl', price:'250.75', stock:'700',NumeroDeLot:'8777',CodeBarre:'998898979797',QuantiteDemandee:'50'}),
        (p2)-[:IS_A_PRODUCT_BELONGING_TO_THAT_BRAND]->(b),


  (b1:Brand {name: 'ANESTHESIQUES',brand:'ANESTHESIQUES',description :'ANESTHESIQUES' }),
    (b1)<-[:IS_A_CATALOG_BELONGING_TO]-(c1:Catalog {name: 'Anesthesiques_Generaux_et_Gaz_medicaux',catalogName:'Anesthesiques_Generaux_et_Gaz_medicaux',brand:'ANESTHESIQUES',brandIdentifier:'ANESTHESIQUES',description :'Anesthésiques généraux et Gaz médicaux'}),
      (c1)<-[:IS_A_PRODUCT_BELONGING_TO_THAT_CATALOG]-(p1:Product {name: 'Kétamine',productName:'Kétamine',brand:'ANESTHESIQUES',brandIdentifier:'ANESTHESIQUES',catalog:'Anesthesiques_Generaux_et_Gaz_medicaux',catalogIdentifier:'Anesthesiques_Generaux_et_Gaz_medicaux',description :'Kétamine',DCI:'Kétamine', Dosage:'50mg/amp', Forme:'Inj', Case:'false', Poste:'false', CTRE:'true', CHR:'true', CHN:'true', Reference:'Kétalar', price:'900.75', stock:'300',NumeroDeLot:'677',CodeBarre:'5454578788',QuantiteDemandee:'10'}),
        (p1)-[:IS_A_PRODUCT_BELONGING_TO_THAT_BRAND]->(b1)

The request that fulfilled my needs

MATCH PATH = (Brand:Brand)<-[*]-(Catalog:Catalog)<-[*]-(Product:Product)
with collect(PATH) as paths 
call apoc.convert.toTree(paths) yield value 
return value

From neo4j 3.5.0 + and up the signature of apoc.convert.toTree we could include/exclude properties,relationships and as has 3 arguments

[paths],[lowerCaseRels=true]) | creates a stream of nested documents representing the at least one root of these paths

like in the below

To exclude we prefix with -

apoc.convert.toTree(ps, true,{nodes: {Catalog: ['-name']}, relationships: {subcategory:['-id']}})

To include

apoc.convert.toTree(ps, true,{nodes: {Catalog: ['name']}, relationships: {subcategory:['id']}})

but Unfortunately there is no way to remove _id _type from cypher result See that link [https://stackoverflow.com/questions/56288402/is-there-a-way-to-remove-id-type-from-cypher-result]1

Cypher request to Generate a nested recursive json

1 Answers1

like in the below

To include