How to create a table based on two queries?

[sandybar]

I have two queries I am trying to join the results together. The first query has the organization details and the second query contains the contact details. I would like to join both organization and contact details into a single table.

The first query:

index="prd-app" event_id="order_placed" sourcetype="data-app" product_id="27" origin="online123"

The results will look like this:

[2022-08-31 11:08:33.580780] [php:notice] pid=15226 cIP=10.10.10.10:56172 event_id="order_placed" app="application_name" log_level="INFO" order_id="123456789" acct_id="123456" user_id="147852" origin="online123" product_id="27" org_name="Example Inc" org_addr1="50th Avenue" org_city="New York" org_state="New York" org_zip="10001" org_country="us" transaction_id="68a26e21add3d5a34184c3e6fde2da6c"

I want to take the acct_id from the first query and use it in a secondary query. However, for the second query this value is not a field value, it's a substring within a json string.

Second query:

index="prd-app" event_id="direct_proxy" sourcetype="org-api" "123456"

I would usually just append the acct_id value like above to get the results. In this case, i'll need the acct_id value to be dynamically added to the query.

The results will look like this:

2022-08-31 11:08:33.580780 DEBUG 1 --- [nio-9005-exec-9] c.d.b.integrations.app.DirectProxy     : transaction_id=68a26e21add3d5a34184c3e6fde2da6c event_id=direct_proxy result={"id":1680770,"account_id":123456,"name":"Example Inc","assumed_name":"","address":"50th Avenue","address2":"","city":"New York","state":"New York","zip":"10001","country":"us","email":"","telephone":"","risk_score":0,"registration_number":"","jurisdiction_city":"","jurisdiction_state":"","jurisdiction_country":"","incorporating_agency":"","contacts":[{"id":147852,"type":"tech","first_name":"Bill","last_name":"Jones","job_title":"Director","email":"bill.jones@example.com","telephone":"","fax":""}]}

Note; the acct_id value is within the Json string, I want to capture the entire result field containing the json string and make that a separate field value combined with the results from the first query.

The table should combine fields from both results:

From query 1: order_id, acct_id, user_id, origin, product_id, org_name, org_addr1, org_city, org_state, org_zip, org_country

From query 2: result