Extracting multiple values having same path in json using json map in sas

[melligeri]

JSON

{
  "totalCount": 2,
  "facets": {},
  "content": [
    [
      {
        "name": "customer_ID",
        "value": "1"
      },
      {
        "name": "customer_name",
        "value": "John"
      }
    ]
  ]
}

How to get the values under "name" using a json map.
i.e i need to get the out as "customer_ID" and "customer_name" using a json map.

Below is my json map.

{
  "DATASETS": [
    {
      "DSNAME": "customers",
      "TABLEPATH": "/root/content",
      "VARIABLES": [
        {
          "NAME": "name",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/name"
        },
        {
          "NAME": "name",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/name"
        },
        {
          "NAME": "value",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/value"
        },
        {
          "NAME": "value",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/value"
        }
        
      ]
    }
  ]
}

The output i am getting is only "customer_name".

Please help.

Thanks.

[BillM_SAS]

I just cut out the duplication in the map file that you supplied.

Map file named ./sasuser/json.map

{
  "DATASETS": [
    {
      "DSNAME": "customers",
      "TABLEPATH": "/root/content",
      "VARIABLES": [
        {
          "NAME": "name",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/name"
        },
        {
          "NAME": "value",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/value"
        }        
      ]
    }
  ]
}

Existing JSON file (named ./sasuser/json.sas):

{
  "totalCount": 2,
  "facets": {},
  "content": [
    [
      {
        "name": "customer_ID",
        "value": "1"
      },
      {
        "name": "customer_name",
        "value": "John"
      }
    ]
  ]
}

SAS code run:

filename mapRef './sasuser/json.map';
libname x json './sasuser/json.sas' map=mapRef;
proc print data=x.customers; run;

OUTPUT:

Obs  name           value 
1    customer_ID    1 
2    customer_name  John 

[ChrisHemedinger]

I answered this on StackOverflow (and Reddit! Hitting all of the channels!) where it seems that the original poster wants the "name" values to be proper SAS variables. Achievable using what @BillM_SAS suggests, plus a transpose step:

libname j json fileref=test;
proc transpose 
 data=j.content 
 out=want;
 id name;
 var value;
run;

Output:

                  customer_    customer_
 Obs    _NAME_       ID          name

  1     value         1          John