Skip to content

json_merge

json_merge(json_string1, json_string2)

Description

Merge json_string1 and json_string2

Examples

Call or Deploy json_merge ?
Call json_merge directly

The easiest way to use bigfunctions

  • json_merge function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy json_merge in your project

Why deploy?

  • You may prefer to deploy json_merge in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

json_merge function can be deployed with:

pip install bigfunctions
bigfun get json_merge
bigfun deploy json_merge

select bigfunctions.eu.json_merge("{\"k1\": \"v1\"}", "{\"k2\": \"v2\"}")

select bigfunctions.us.json_merge("{\"k1\": \"v1\"}", "{\"k2\": \"v2\"}")

select bigfunctions.europe_west1.json_merge("{\"k1\": \"v1\"}", "{\"k2\": \"v2\"}")

+--------------------------+
| merged_json              |
+--------------------------+
| {"k1": "v1", "k2": "v2"} |
+--------------------------+
Need help or Found a bug using json_merge?
Get help using json_merge

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about json_merge

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Use cases

You have a table with customer data split into two JSON string columns, perhaps due to limitations in how the data was initially collected or stored. You want to combine these two JSON strings into a single, unified JSON object for easier querying and analysis.

Example Scenario:

Imagine a table named customers with columns customer_info_1 and customer_info_2 containing JSON strings:

| customer_id | customer_info_1        | customer_info_2           |
|-------------|-------------------------|--------------------------|
| 1           | '{"name": "John Doe", "age": 30}' | '{"city": "New York", "country": "USA"}' |
| 2           | '{"name": "Jane Smith", "age": 25}' | '{"city": "London", "country": "UK"}'   |

Query using json_merge:

SELECT
    customer_id,
    bigfunctions.us.json_merge(customer_info_1, customer_info_2) AS merged_customer_info
FROM
    `your_project.your_dataset.customers`;

Result:

| customer_id | merged_customer_info                                           |
|-------------|-----------------------------------------------------------------|
| 1           | '{"name": "John Doe", "age": 30, "city": "New York", "country": "USA"}' |
| 2           | '{"name": "Jane Smith", "age": 25, "city": "London", "country": "UK"}'   |

This now provides a single merged_customer_info column containing all customer data in a single JSON object, making it much easier to query using BigQuery's JSON functions. For example, you could now easily find all customers in the UK with:

SELECT *
FROM `your_project.your_dataset.customers`
WHERE JSON_VALUE(merged_customer_info, '$.country') = 'UK';

This is just one example. Other uses could include:

  • Combining data from different APIs: If you're pulling data from multiple APIs that return JSON, json_merge can help combine the responses into a single object.
  • Progressive profiling: You might gather user data in stages, storing each stage in a separate JSON string. json_merge allows you to consolidate this data as it becomes available.
  • Simplifying data storage: Instead of having multiple JSON columns, you can combine them into one, making your table schema cleaner and easier to manage.

It's important to note how json_merge handles conflicts. If both JSON strings have the same key, the value from the second JSON string (json_string2) will overwrite the value from the first. This behavior is important to consider when designing your data model.

Spread the word!

BigFunctions is fully open-source. Help make it a success by spreading the word!

Share on Add a on