LOOKUP Metadata Action Tag

LOOKUP Metadata Action Tag
lac52
You can enable API users to POST data by using the 
LOOKUP
 metadata action tag. The schema uses the keys that 
CA Live API Creator
 generates.
In this article:
 
 
3
 
 
Background
Consider an API for partners to place orders. Imagine that the 
Items
 resource includes a foreign key to the 
Product
 table-based resource, where the key is a DBMS-generated key. The API user knows a product name but does not know the product number within 
CA Live API Creator
.
You can provide an API that accepts product names without having to know product numbers (typically the foreign key) by defining your resources that expose the appropriate attributes and that support JSON definitions. The JSON definitions provide lookups by way of the 
LOOKUP
 metadata action tag to compute foreign keys, using natural keys, so API users do not need to provide them. 
Example: The Demo API Sample
The 
Demo
 API sample includes the 
OrderLookupParent
 table-based resource. You can insert an order by invoking a PUT request with the following JSON to the 
OrderLookupParent
 resource:
{
"@metadata": {"action":"INSERT"},
"Customer": {
"@metadata":
{
"action":"LOOKUP",
"key":"Name"
},
"Name":"Alpha and Sons"
},
"Items":[
{
"Product":
{
"@metadata": {
"action":"LOOKUP",
"key":"ProductName"
},
"ProductName":"Hammer"
},
"Quantity": 1
}
]
}
For the lookup to happen, within the JSON, the 
@metadata
 object must:
  • Include an 
    action
     attribute with a value of 
    LOOKUP
    .
  • Include a 
    key
     attribute with a value that must be a single attribute or an array of attribute names. The API uses the attribute to find the object in question. 
  • Contain the attributes that are referenced in the 
    key
     attribute.
You can pre-process, and alter, incoming requests using request events. You can insert the 
@metadata
 action tag without requiring it from the client.
For more information about how to provide entry points using request events, see Event Handlers.
Lookup Processing Where the Key Is Included Within the JSON
The previous JSON provides the 
@metadata
 key within the JSON. 
CA Live API Creator
 uses the 
LOOKUP
 metadata action at two points. The first 
LOOKUP
 metadata action is a lookup for a customer using the 
Name
 key property. The second 
LOOKUP
 metadata action is where the 
ProductName
 attribute is the effective key and is a lookup for an Item's 
product_number
. The 
ProductName
 key dictates lookup processing and identifies the fields in the JSON segment (
Product
) that 
CA Live API Creator
 uses to lookup the product, as follows.
 
CA Live API Creator
:
  1. De-aliases 
    ProductName
     to 
    name
    .
    The string can be an array of attribute names.
  2. Uses the fields to construct a SQL command to a query on 
    Product
     .
    The fields do not need to comprise a primary or unique key, though in practice they typically do.
  3. (If a row is returned) Assigns the foreign key (which might be the 
    key
     fields) to the containing resource. In this case, the Item's 
    product_number
     is set.
The Business to Business (B2B) sample includes an example of lookups. For more information about this example, see the B2B API Sample.
Lookup Processing Where the Key Is Defined in the Resource
If you do not provide the 
@metadata
 key within the JSON, 
CA Live API Creator
 uses the resource attribute that you have added to the table-based resource and that is selected as the effective primary key (the 
Key
 checkbox for the resource attribute is selected). If it does not find the defined key for the resource, it uses the primary key for the underlying table.
For more information about how to define resource attributes as effective primary keys for the resource, see Manage Resource Attributes.
Example:
 
The following example illustrates modifying the 
OrderLookupParent
 resource (in the 
Demo
 API sample) to use the key that is defined within the resource:
  1. Click the 
    Product
     subresource from the list of resources.
  2. Select the 
    Key
     checkbox for the 
    name
     column (the alias is 
    ProductName
    ), and then save your changes.
  3. Click 
    Test
    .
  4. In the REST Lab, PUT the following JSON (without the key) to the 
    OrderLookupParent
     resource to insert an order:
    {
    "@metadata": {"action":"INSERT"},
    "Customer": {
    "@metadata": {
    "action":"LOOKUP",
    "key":"Name"
    },
    "Name":"Alpha and Sons"
    },
    "Items":[
    {
    "Product":
    {
    "@metadata": {
    "action":"LOOKUP"
    },
    "ProductName":"Hammer"
    },
    "Quantity": 1
    }
    ]
    }
Find or Update Rows from External Sources using the Metadata Key
It is a common pattern to find or update rows from external sources, where the system key is not known. This is often because the actual key might be a database-generated number, unknown to external systems or businesses.
For example, the Business to Business (B2B) sample test posts an order for Products. Products are identified by a 
ProductID
. Since external systems cannot know such names, the test uses the 
LOOKUP
 metadata action tag, for example:
{
"CustomerNumber":"VINET",
"Items":[
{
"Product":{
"@metadata":{
"action":"LOOKUP",
"key":"ProductName"
},
"ProductName":"Pavlova"
},
"Quantity":1
},
 
CA Live API Creator
 uses the 
key
 to find a Product row by 
ProductName
 , extracts its 
ProductID
 , and stores this in the 
Items
 row being inserted. The 
LOOKUP
 metadata action tag automates this. While the fields comprising the key typically comprise a unique key, this is not strictly required. The only requirement is that the find returns exactly one row.
The 
key
 is also available in the 
MERGE_INSERT
 metadata action tag.