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

Question

Dose anyone can help me get multiple values in a json having same path using a json map. Any help is appreciated. Thank you.

JSON

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

JSON MAP

{
  "DATASETS": [
    {
      "DSNAME": "customers",
      "TABLEPATH": "/root/content",
      "VARIABLES": [
        {
          "NAME": "name",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/name"/*output as customer_ID*/
        },
        {
          "NAME": "name",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/name"/*output as customer_name*/
        },
        {
          "NAME": "value",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/value"/*output as 1*/
        },
        {
          "NAME": "value",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/value"/*output as John*/
        }
        
      ]
    }
  ]
}

When i use the above json map I get the output for name as only "customer_name", but i need both "customer_ID" and "customer_name" in the output.

Similarly i need both values of "value"

Answer 1

JSON is a hierarchy of name-value pairs. The JSON engine in SAS will take the "name" and assign as a variable name, and then populate with the value. In your JSON, there are two sets of name-values, one being the name of an intended variable, and another being its value. This is a common output scheme we find in GraphQL responses -- and these require a little manipulation to turn into 2-D data sets.

For your example, you could use PROC TRANSPOSE:

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  

You can also do more manual seek/assignment by using DATA step to process what you see in the ALLDATA member in the JSON libname. In your example, SAS sees that as:

Obs    P    P1             P2      V    Value

  1    1    totalCount             1    2            
  2    1    facets                 0                 
  3    1    content                0                 
  4    1    content                0                 
  5    2    content       name     1    customer_ID  
  6    2    content       value    1    1            
  7    1    content                0                 
  8    2    content       name     1    customer_name
  9    2    content       value    1    John     

Processing the ALLDATA member is not as friendly as using the relational data that the JSON engine can create, but I find with GraphQL responses that's what you need to do to get more control over the name, length, and type/format for output variables.