How to Use XQuery to Print Specific Keys in JSON Without Arrays

Discover step-by-step solutions on how to extract keys from JSON using `XQuery`, while ignoring keys with array values.
---
This video is based on the question https://stackoverflow.com/q/73446713/ asked by the user 'Balan' ( https://stackoverflow.com/u/13281489/ ) and on the answer https://stackoverflow.com/a/73450614/ provided by the user 'Mads Hansen' ( https://stackoverflow.com/u/14419/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: I want to write the XQuery to print the specific keys in JSON and want to except if it has an array value

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Extracting Specific Keys from JSON with XQuery

In an age where data formats like JSON are ubiquitous, developers often need to extract specific elements based on certain conditions. One common task is to retrieve keys from a JSON object while ignoring those that have array values. In this post, we’ll tackle how to do this using XQuery, particularly in the context of a JSON document read from a MarkLogic database.

Problem Statement

You have a JSON structure and you want to extract certain keys, but you need to avoid keys that contain array values. Here’s an example JSON structure we’ll use for demonstration:

[[See Video to Reveal this Text or Code Snippet]]

Expected Output

For the above JSON, we want to extract the following keys:

[[See Video to Reveal this Text or Code Snippet]]

We want to exclude keys that contain object or array values, such as transation from our results.

Solution

To solve this issue, we can leverage XQuery, which is specifically designed for querying XML and JSON data. Below, we break down the steps needed to accomplish this task.

Iterating Through the JSON Properties

First, we need to read the JSON document and iterate over its property nodes. We will do this using the doc() function that represents our JSON document in MarkLogic.

Using the Appropriate Function

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

for $property in doc("/test.json")/object-node()/node(): This line retrieves all nodes from the JSON document, specifically targeting object nodes which contain properties.

where not($property/node()): This condition filters out any property that has child nodes, effectively removing keys that correspond to arrays or objects.

return $property/name(): Finally, this returns just the names of the properties that meet our conditions (i.e., keys without child nodes).

Alternative Approach: Excluding Object and Array Instances

Alternatively, if you want to explicitly filter out keys that are instances of object or array nodes, you can do the following:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Alternative Code

not($property instance of object-node() or $property instance of array-node()): This line checks if the property is neither an object node nor an array node before including it in the result.

Summary

By using XQuery, you can easily extract the specific keys from a JSON structure while excluding those that have array values. Whether you prefer to filter based on the presence of child nodes or by explicitly checking the node type, both methods effectively achieve the desired outcome.

Key Option 1: Use property child node checking.

Key Option 2: Exclude based on node type.

With this knowledge, you should be able to manipulate and query JSON data more effectively within your MarkLogic applications.

Feel free to experiment with these code snippets in your environment, and tailor them according to your specific JSON structures and needs.