LOOKUP Metadata Action Tag

You can enable API users to POST data by using the
LOOKUP
metadata action tag. The schema uses the keys that
Layer7 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
Layer7 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 pipeline events. You can insert the
@metadata
action tag without requiring it from the client.
For more information about how to provide entry points using pipeline events, see Pipeline Events.
Lookup Processing Where the Key Is Included Within the JSON
The previous JSON provides the
@metadata
key within the JSON. API Server 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 API Server uses to lookup the product, as follows.
API Server does the following:
  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,
Layer7 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, insert an order by issuing a PUT request with the following JSON (without the key) to the
    OrderLookupParent
    resource:
    {
    "@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
},
Layer7 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.