Masking JSON Data in PostgreSQL

Important note on attribute ordering:
When processing JSON or JSONB, the order of attributes in each JSON object cannot be guaranteed. Attributes are written out alphabetically. While this does not affect how data reading software interprets the file, it may appear unusual when inspecting the file manually.

Having established a foundation in data masking and data generation, we now turn to JSON data. Although this adds complexity, DATPROF Privacy can detect JSON structures and supports JSONPath-based queries.

In our sample data is a table called “cars“. It contains an id of the datatype integer and data column of the type JSONB. If you observe the table in our Gasware database explorer it should look like this.

You can perform a pgSQL query in your database editor to view the underlying data.

Using the JSONPath query language we can retrieve parts of this data and mask it or generate new data to replace it.

Now lets explore this function!

In DATPROF Privacy select the cars table
Rightclick the “data” column on the right
From the context menu choose Add function → Generate

JSONPath is a query language used to extract data from JSON objects. To retrieve the correct information, you need to understand the structure and nesting of the JSON data you are querying. In our example, the first level of nesting contains three attributes: age, car, and name. The car object includes additional nested attributes one level deeper, such as model and model type.

Because name is a top-level attribute, no additional path segments are required. The value can be accessed directly with $.name.
Select the First name generator from the generator drop down list
Select as language US
Click OK