Location>code7788 >text

Make the query work with the json path

Popularity：486 ℃/2024-10-14 13:08:06

A record of recent refinements

1. Make it possible to use the json path for queries

Sometimes we will store json to the db, and sometimes we will only fetch the json part of the data, or filter through the json part of the data

So also support these scenarios, (currently only db implementation, json operations are relying on db json functions)

Examples:
digital

("""
    INSERT INTO Weather
    (name, value)
    VALUES ('Hello', '{"a":2}'),('A', '{"a":3,"c":[4,5,{"f":7}]}')
    """);

Then configure the field to allow json

 [Db()]
 [Table(nameof(Weather))]
 public class Weather
 {
     [Select, Where, OrderBy]
     public string Name { get; set; }

     [Select(Field = "Value"), Where, OrderBy, Column(IsJson = true)]
     public string V { get; set; }
 }

The api methods don't need to be implemented additionally

[HttpGet]
public async Task<object> Selects()
{
    return await <Weather>();
}

Users querying the api can do whatever they want with the json fields, such as

curl --location 'http://localhost:5259/weather?Fields=v,json(v,'$.a',vvva)&OrderBy=json(v,'$.a') asc&Where=json(v,'$.a') != 1'

in the end

{
    "totalCount": null,
    "rows": [
        {
            "vvva": 2,
            "v": "{\"a\":2}"
        },
        {
            "vvva": 3,
            "v": "{\"a\":3,\"c\":[4,5,{\"f\":7}]}"
        }
    ]
}

ps: json implementation of the corresponding db json function

db json function
SQLite json_extract
PostgreSQL jsonb_path_query_first
MySql json_unquote(json_extract())
sql server JSON_QUERY

2. Field whitelist validation

By default, the parsed statement results will be validated against the fields, and those that don't pass will return 400

Validation:

  • Not in the whitelist range of the field configuration
  • No analogies to 1 = 1 are allowed, only field = 1.
  • Unconfigured json fields do not allow the use of json functions

If you want to change the validation logic or validate yourself, you can do so via theSelectStatementOptions take matters into one's own hands

public record class SelectStatementOptions
{
    public bool AllowNotFoundFields { get; init; } = false;
    public bool AllowNonStrictCondition { get; init; } = false;
    public Action<Statement> Visiter { get; init; } = null;
}

3. swagger generation

Install swagger

<PackageReference Include="" Version="0.0.2.3" />

swagger gen configuration method

(c =>
{
    ();
});

api method configuration swagger

[DbSwaggerByType(typeof(Weather))]
[HttpGet]
public async Task<object> Selects()
{
    return await <Weather>();
}

Simply configure these and swagger will automatically generate the field descriptions for you!

4. Main features have been refined and released to nuget.

To try it, simply install the required package

<PackageReference Include="" Version="0.0.2.3" />
<PackageReference Include="" Version="0.0.2.3" />
<PackageReference Include="" Version="0.0.2.3">
  <PrivateAssets>all</PrivateAssets>
  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="" Version="0.0.2.3" />
<PackageReference Include="" Version="0.0.2.3" />
<PackageReference Include="" Version="0.0.2.3" />
<PackageReference Include="" Version="0.0.2.3" />
<Previous Next>