Use this tag for tasks involving queries against a JSON document, such as extracting JSON components from a JSON text. The tag can also be used for query languages intended for specifying such queries.
Questions tagged [json-query]
254 questions
28
votes
5 answers
How to search SQL column containing JSON array
I have a SQL column that has a single JSON array:
{"names":["Joe","Fred","Sue"]}
Given a search string, how can I use SQL to search for a match in the names array? I am using SQL 2016 and have looked at JSON_QUERY, but don't know how to search for…
paultechguy
- 2,318
- 4
- 25
- 34
16
votes
2 answers
MySQL nested JSON column search and extract sub JSON
I have a MySQL table authors with columns id, name and published_books. In this, published_books is a JSON column. With sample data,
id | name | published_books
-----------------------------------------------------------------------
1 | Tina | …
Kanmaniselvan
- 522
- 1
- 8
- 23
12
votes
1 answer
Reading json array into rows in SQL Server
Given the sample json data below, how can I write a query to pull the array data all in one step? My goal is to have one row for each item in the ActionRecs array (4). My actual json is more complicated but I think this gives a good example of my…
pretzelb
- 1,131
- 2
- 16
- 38
9
votes
3 answers
SQL Server 2016 select where in json array
I have a table [JsonTable], and the column [JsonData] save the json string,
JsonData like:
{
"Names": ["John", "Joe", "Sam"]
}
How can I inner join this table like:
SELECT* FROM [TestTable] AS T
INNER JOIN [JsonTable] AS J ON T.[Name] IN…
Max
- 4,439
- 2
- 18
- 32
8
votes
2 answers
how to fix SQL server Json text is not properly formatted. Unexpected character is found at position 151
Im working on a table that has a column in JSON format and I want to extact the coordinate value from that column.
So I run the following code:
Select *,JSON_VALUE(field,'$."Coordinate-X"[0]') As coordinate INTO TABLE_1 FROM table
WHERE…
Zeir
- 249
- 1
- 4
- 13
8
votes
4 answers
Where clause on json data in Sql Server 2016
I have a nvarchar(1000) field in my table and I am storing JSON data in that column.
eg :
CONTENT_RULE_ID CONTENT_RULE
1 {"EntityType":"Inquiry", "Values":[1,2]}
2 {"EntityType":"Inquiry", "Values":[1,3]}
3 …
Harisyam M
- 407
- 1
- 3
- 14
7
votes
1 answer
SQL Server - "for json path" statement does not return more than 2984 lines of JSON string
I'm trying to generate huge amount of data in a complex and nested JSON string using "for json path" statement, and I'm using multiple functions to create different parts of this JSON string, as follow:
declare @queue nvarchar(max)
select @queue =…
Brian Salehi
- 414
- 2
- 10
- 19
6
votes
3 answers
mongoexport - issue with JSON query (extended JSON - Invalid JSON input)
I have started learning MongoDB recently. Today the instructor taught us the mongoexport command. While practicing the same, I face a typical issue which none of the other batchmates including the instructor faced. I use MongoDB version 4.2.0 on my…
itsraghz
- 857
- 1
- 11
- 25
6
votes
4 answers
How I can use Variables in Ansible json_query?
I want to use a ansible variable inside the json query filter.
This is my Code:
Playbook execution:
ansible-playbook debug.yml -e "project_environment=live"
- debug:
msg: "{{ project_environment }}"
- debug:
msg: "{{ check_objects |…
bswe
- 100
- 1
- 1
- 8
6
votes
2 answers
JS Object Query Syntax
I'm trying to find a way filter js collection with a query syntax similar to SQL.
The only library I found to accomplish this task is json-query.
It works in some cases but it has limitations. It isn't possible to query on different object levels…
Pietro
- 1,815
- 2
- 29
- 63
6
votes
1 answer
C# Dapper using JSON_VALUE for SQL Server 2016
I want to query data from my table using JSON_VALUE:
var str = "123";
var value = "Name"
using(var conn = GetMyConnection())
{
var result = conn.QueryFirstOrDefault(
@"SELECT [Id] FROM [dbo].[MyTable]
WHERE…
Max
- 4,439
- 2
- 18
- 32
5
votes
1 answer
ansible json-query path to select item by content
Does anyone know what json-query filter can be used to select Tigger's food in the sample JSON below? The JSON is a simplified stand-in for a massive and relatively complicated AWS blob.
Some background: I was rather pleased to discover that…
Max Murphy
- 1,701
- 1
- 19
- 29
4
votes
2 answers
How to divide and multiply Ansible facts queried by json_query?
"{{ ansible_facts | json_query('mounts[*].size_available') }} / {{ ansible_facts | json_query('mounts[*].size_total') }} * 100"
I am trying to get size available and divide it by size total and Multiply it by 100 to give me the percentage disc…
Ali
- 55
- 5
4
votes
2 answers
Filter elements containing string with JMESPath
I want to get a list of addresses of a defined interface type.
I found some info here.
Here is my playbook:
- name: Test JMESPath
hosts: localhost
gather_facts: no
vars:
interfaces:
- name: em0
address: 10.127.37.89/29
-…
mooky
- 87
- 5
4
votes
2 answers
json format query with contains
I have the following json output in ansible:
[{
"active_transaction": null,
"cores": 4,
"hostname": "alpha-auth-wb01"
},
{
"active_transaction": null,
"cores": 4,
"hostname": "beta-auth-wb01"
}]
Now I am trying to filter the…
user6482708